Application Development Guide

Trigger Granularity

When a trigger is activated, it runs according to its granularity as follows:

FOR EACH ROW

It runs as many times as the number of rows in the set of affected rows.

FOR EACH STATEMENT

It runs once for the entire trigger event.

If the set of affected rows is empty (that is, in the case of a searched UPDATE or DELETE in which the WHERE clause did not qualify any rows), a FOR EACH ROW trigger does not run. But a FOR EACH STATEMENT trigger still runs once.

For example, keeping a count of number of employees can be done using FOR EACH ROW.

    CREATE TRIGGER NEW_HIRED 
      AFTER INSERT ON EMPLOYEE 
      FOR EACH ROW MODE DB2SQL 
      UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1 

You can achieve the same affect with one update by using a granularity of FOR EACH STATEMENT.

    CREATE TRIGGER NEW_HIRED 
      AFTER INSERT ON EMPLOYEE 
      REFERENCING NEW_TABLE AS NEWEMPS 
      FOR EACH STATEMENT MODE DB2SQL 
      UPDATE COMPANY_STATS 
      SET NBEMP = NBEMP + (SELECT COUNT(*) FROM NEWEMPS) 

[ DB2 List of Books | Search the DB2 Books ]