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] ...