Oracle PL/SQL Programming, 5th Edition

DML Triggers

Data Manipulation Language (DML) triggers fire when records are inserted into, updated within, or deleted from a particular table, as shown in Figure 19-1. These are the most common types 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, MERGE, or DELETE statements, or combinations of these three. Starting with Oracle Database 11g, you can bundle together several DML triggers into one compound trigger.

There are also many ways to actually configure DML triggers. To determine what works for your environment, you need to answer the following questions:

• Should the triggers fire once for the whole DML statement or once for each row involved in the statement?

• Should the triggers fire before or after the whole statement completes or before or after each row is processed?

• Should the triggers fire for inserts, updates, deletes, or a combination thereof?

Figure 19-1. DML trigger flow of control

DML Trigger Concepts

Before diving into the syntax and examples, you may find it useful to review these DML trigger concepts and associated terminology:

BEFORE trigger

A trigger that executes before a certain operation occurs, such as BEFORE INSERT.

AFTER trigger ...