Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL for DBAs

DML Triggers

Data Manipulation Language (DML) triggers fire when records are inserted into, updated within, or deleted from a particular table. These are the most common type of triggers, especially for developers; the other trigger types are used primarily by DBAs.

There are many options regarding DML triggers. They can fire after or before a DML statement or they can fire after or before each row is processed within a statement. They can fire for INSERT, UPDATE, or DELETE statements, or combinations of the three.

Transaction participation

By default, DML triggers participate in the transaction from which they were fired. This means that:

  • If a trigger raises an exception, that part of the transaction will be rolled back.

  • If the trigger performs any DML itself (such as inserting a row into a log table), then that DML becomes a part of the main transaction.

  • You cannot issue a COMMIT or ROLLBACK from within a DML trigger.

Tip

If you define your DML trigger to be an autonomous transaction, however, then any DML performed inside the trigger will be saved or rolled back—with your explicit COMMIT or ROLLBACK statement—without affecting the main transaction.

The following sections present the syntax for creating a DML trigger, provide reference information on various elements of the trigger definition, and explore an example that uses the many components and options for these triggers.

Creating a DML trigger

To create (or replace) a DML trigger, use the syntax shown here:

 1 CREATE [OR REPLACE] ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page