Designing triggers

• an INSERT statement
• an UPDATE statement, or a referential constraint which performs an UPDATE
• a DELETE statement, or a referential constraint which performs a DELETE
• a MERGE statement

You must also define the action, called the triggered action, that the trigger performs when its trigger event occurs. The triggered action consists of one or more statements which can execute either before or after the database manager performs the trigger event. Once a trigger event occurs, the database manager determines the set of rows in the subject table that the modify operation affects and executes the trigger.

Guidelines when creating triggers:

When creating a trigger, you must declare the following attributes and behavior:

• The name of the trigger.
• The name of the subject table.
• The trigger activation time (BEFORE or AFTER the modify operation executes).
• The trigger event (INSERT, DELETE, or UPDATE).
• The old transition variable value, if any.
• The new transition variable value, if any.
• The old transition table value, if any.
• The new transition table value, if any.
• The granularity (FOR EACH STATEMENT or FOR EACH ROW).
• The triggered action of the trigger (including a triggered action condition and triggered statement(s)).
• If the trigger event is UPDATE a trigger-column list if the trigger should only fire when specific columns are specified in the update statement.

Designing multiple triggers:

When triggers are defined using the CREATE TRIGGER statement, their creation time is registered in the database in form of a timestamp. The value of this timestamp is subsequently used to order the activation of triggers when there is more than one trigger that should be run at the same time. For example, the timestamp is used when there is more than one trigger on the same subject table with the same event and the same activation time. The timestamp is also used when there are one or more AFTER or INSTEAD OF triggers that are activated by the trigger event and referential constraint actions caused directly or indirectly (that is, recursively by other referential constraints) by the triggered action.

Consider the following two triggers:

    CREATE TRIGGER NEW_HIRED 
      AFTER INSERT ON EMPLOYEE 
      FOR EACH ROW
      BEGIN ATOMIC 
        UPDATE COMPANY_STATS 
        SET NBEMP = NBEMP + 1; 
      END
  
    CREATE TRIGGER NEW_HIRED_DEPT 
       AFTER INSERT ON EMPLOYEE 
       REFERENCING NEW AS EMP 
       FOR EACH ROW
         BEGIN ATOMIC 
           UPDATE DEPTS 
           SET NBEMP = NBEMP + 1 
           WHERE DEPT_ID = EMP.DEPT_ID; 
         END

The preceding triggers are activated when you run an INSERT operation on the employee table. In this case, the timestamp of their creation defines which of the preceding two triggers is activated first.

The activation of the triggers is conducted in ascending order of the timestamp value. Thus, a trigger that is newly added to a database runs after all the other triggers that are previously defined.

Old triggers are activated before new triggers to ensure that new triggers can be used as incremental additions to the changes that affect the database. For example, if a triggered statement of trigger T1 inserts a new row into a table T, a triggered statement of trigger T2 that is run after T1 can be used to update the same row in T with specific values. Because the activation order of triggers is predictable, you can have multiple triggers on a table and still know that the newer ones will be acting on a table that has already been modified by the older ones.

Trigger interactions with referential constraints:

A trigger event can occur as a result of changes due to referential constraint enforcement. For example, given two tables DEPT and EMP, if deleting or updating DEPT causes propagated deletes or updates to EMP by means of referential integrity constraints, then delete or update triggers defined on EMP become activated as a result of the referential constraint defined on DEPT. The triggers on EMP are run either BEFORE or AFTER the deletion (in the case of ON DELETE CASCADE) or update of rows in EMP (in the case of ON DELETE SET NULL), depending on their activation time.