The interactions in this eCourse require Javascript to be enabled.
Creating DML Triggers: Part 1
previous|next
Syntax for creating a DML trigger
CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON object_name [ [REFERENCING OLD AS old/NEW AS new] FOR EACH ROW [WHEN (condition)] ] trigger_body
The components of the syntax are:
trigger_name
trigger_name uniquely identifies the trigger.
timing
Timing indicates when the trigger fires in relation to the triggering event. Values are BEFORE, AFTER or INSTEAD OF.
event
Event identifies the DML operation causing the trigger to fire. Values are INSERT, UPDATE and DELETE.
object_name
object_name indicates the table or view associated with the trigger.
REFERENCING
REFERENCING clause is used to choose a correlation name for referencing the old and new values of the current row.
WHEN
WHEN clause is used to apply a conditional predicate, in parentheses, which is evaluated for each row to determine whether or not to execute the trigger body.
trigger_body
The trigger_body is the action performed by the trigger.
Types of DML Triggers:
Statement trigger
A statement trigger is the default type of trigger and executes once for the triggering event. A statement trigger fires even if now rows are affected at all.
Row trigger.
A row trigger executes once for each row affected by the triggering event. A row trigger is not executed if the triggering event does not affect any rows. To indicate a row trigger, the FOR EACH ROW clause is specified.