AFTER SQL triggers

 

An after trigger runs after the corresponding insert, update, or delete changes are applied to the table.

The WHEN condition can be used in an SQL trigger to specify a condition. If the condition evaluates to true, the SQL statements in the SQL trigger routine body are run. If the condition evaluates to false, the SQL statements in the SQL trigger routine body are not run, and control is returned to the database system.

In the following example, a query is evaluated to determine if the statements in the trigger routine body should be run when the trigger is activated.

CREATE TABLE TodaysRecords(TodaysMaxBarometricPressure FLOAT, 
  TodaysMinBarometricPressure FLOAT)


CREATE TABLE OurCitysRecords(RecordMaxBarometricPressure FLOAT, 
  RecordMinBarometricPressure FLOAT)


CREATE TRIGGER UpdateMaxPressureTrigger 

AFTER UPDATE OF TodaysMaxBarometricPressure ON TodaysRecords REFERENCING NEW AS new_row FOR EACH ROW MODE DB2ROW

WHEN (new_row.TodaysMaxBarometricPressure>
     (SELECT MAX(RecordMaxBarometricPressure) FROM
     OurCitysRecords))
  UPDATE OurCitysRecords          SET RecordMaxBarometricPressure =
             new_row.TodaysMaxBarometricPressure 

CREATE TRIGGER UpdateMinPressureTrigger AFTER UPDATE OF TodaysMinBarometricPressure ON TodaysRecords REFERENCING NEW AS new_row FOR EACH ROW MODE DB2ROW

WHEN(new_row.TodaysMinBarometricPressure<
     (SELECT MIN(RecordMinBarometricPressure) FROM
     OurCitysRecords))
  UPDATE OurCitysRecords          SET RecordMinBarometricPressure =
             new_row.TodaysMinBarometricPressure

First the current values are initialized for the tables.

INSERT INTO TodaysRecords VALUES(0.0,0.0)

INSERT INTO OurCitysRecords VALUES(0.0,0.0)

For the SQL update statement below, the RecordMaxBarometricPressure in OurCitysRecords is updated by the UpdateMaxPressureTrigger.

UPDATE TodaysRecords SET TodaysMaxBarometricPressure = 29.95

But tomorrow, if the TodaysMaxBarometricPressure is only 29.91, then the RecordMaxBarometricPressure is not updated.

UPDATE TodaysRecords SET TodaysMaxBarometricPressure = 29.91

SQL allows the definition of multiple triggers for a single triggering action. In the previous example, there are two AFTER UPDATE triggers: UpdateMaxPressureTrigger and UpdateMinPressureTrigger. These triggers are activated only when specific columns of the table TodaysRecords are updated.

AFTER triggers may modify tables. In the example above, an UPDATE operation is applied to a second table. Note that recursive insert and update operations should be avoided. The database management system terminates the operation if the maximum trigger nesting level is reached. You can avoid recursion by adding conditional logic so that the insert or update operation is exited before the maximum nesting level is reached. The same situation needs to be avoided in a network of triggers that recursively cascade through the network of triggers.

 

Parent topic:

SQL triggers