Triggers

A further use of compound statements is in the use of triggers in SQL. A trigger is defined for use with a table (subject table), where the table is updated by a method specified by the compound statement when the trigger is executed. A trigger is is quite likely to be triggered without the user being aware of the fact.

The hidden effect of a trigger is comparable to the referential actions associated with foreign keys.

The trigger below demonstrates the automatic updating of one table triggered by the updating of a different table.

CREATE TRIGGER add_enrolment 
   AFTER INSERT ON mod_enrolment 
   REFERENCING NEW AS new_enrolment 
   FOR EACH ROW
BEGIN
   UPDATE mod_course
   SET student_count = student_count + 1
   WHERE mod_course.course_code = new_enrolment.course_code;
END

What it all means

• INSERT - specifies the particular update that will invoke the trigger. Other keywords are DELETE and UPDATE.
• AFTER - specifies when the trigger should be fired. The alternative keyword is BEFORE.
• REFERENCING NEW - in these statement a name is provided in the context of new rows of data.
• Where DELETE is used a name may refer to OLD data.
• Where UPDATE is used it may specify names to refer to both OLD and NEW data.
• The compound statement that lies between BEGIN and END specifies what action is to happen when the trigger is fired.

Triggers can be used in a variety of situations including:

• When the updating of one table requires the updating of a second table.
• Dynamic constraints that cannot by the normal SQL facilities.

Triggers do not have any privileges associated with them. However, the owner of a trigger that references a subject table not belonging to them, must have the appropriate privileges for that table.

A trigger should be seen as part of the processing of the triggering statement, and all processing must be completed successfully or not at all.

Comments, suggestions, ideas to
Stuart Banner