The syntax for the CREATE TRIGGER statement is fairly involved, but you can break it down into understandable pieces. First take a look at the overall picture:
CREATE TRIGGER trigger_name
trigger_action_timetrigger_event
ON table_name
[REFERENCING old_or_new_value_alias_list]
triggered_action
The trigger name is the unique identifier for this trigger. The trigger action time is the time you want the triggered action to occur: either BEFORE or AFTER the triggering event. The fact that a triggered action can occur before the event that is supposedly causing it to happen may seem a little bizarre, but in some cases, this ability can be very useful.Because the database engine knows that it is about to execute a triggering event before it actually executes it, it has the ability to sandwich in the triggered event ahead of the execution of the triggering event, if a trigger action time of BEFORE has been specified.
Three possible trigger events can cause a trigger to fire: the execution of an INSERT statement, a DELETE statement, or an UPDATE statement. These three statements have the power to change the contents of a database table.
Thus, any insertion of one or more rows into the subject table, any deletion of one or more rows from the subject table, or any update of one or more columns in one or more rows in the subject table can cause a trigger to fire. ON table_name, of course, refers to the table for which an INSERT, DELETE, or UPDATE has been specified.Statement and row triggers
The triggered_action in the preceding example has the following syntax:[ FOR EACH { ROW | STATEMENT }]
WHEN <left paren><search condition><right paren>
<triggered SQL statement>
You can specify how the trigger will act:
-
Row trigger: The trigger will fire once upon encountering the INSERT, DELETE, or UPDATE statement that constitutes the triggering event.
-
Statement trigger: The trigger will fire multiple times, once for every row in the subject table that is affected by the triggering event.
When a trigger fires
The search condition in the WHEN clause enables you to specify the circumstances under which a trigger will fire. Specify a predicate, and if the predicate is true, the trigger will fire; if it’s false, it won’t. This capability greatly increases the usefulness of triggers.You can specify that a trigger fires only after a certain threshold value has been exceeded, or when any other condition can be determined to be either True or False.
The triggered SQL statement
The triggered SQL statement can be a single SQL statement or a sequence of SQL statements executed one after another. In the case of a single SQL statement, the triggered SQL statement is merely an ordinary SQL statement.For a sequence of SQL statements, however, you must guarantee atomicity to ensure that the operation is not aborted midstream, leaving the database in an unwanted state. You can do this with a BEGIN-END block that includes the ATOMIC keyword:
BEGIN ATOMIC
{ SQL statement 1 }
{ SQL statement 2 }
...
{ SQL statement <i>n</i> }
END
An example trigger definition
Suppose the corporate human resources manager wants to be informed whenever one of the regional managers hires a new employee. The following trigger can handle this situation nicely:CREATE TRIGGER newhire
BEFORE INSERT ON employee
FOR EACH STATEMENT
BEGIN ATOMIC
CALL sendmail ('HRDirector')
INSERT INTO logtable
VALUES ('NEWHIRE', CURRENT_USER, CURRENT_TIMESTAMP);
END;
Whenever a new row is inserted into the NEWHIRE table, an e-mail is fired off to the HR manager with the details, and the logon name of the person making the insertion and the time of the insertion are recorded in a log table, providing an audit trail.