SQL trigger transition tables

 

An SQL trigger might need to refer to all of the affected rows for an SQL insert, update, or delete operation. For example, a trigger needs to apply aggregate functions, such as MIN or MAX, to a specific column of the affected rows. The OLD_TABLE and NEW_TABLE transition tables can be used for this purpose.

In the following example, the trigger applies the aggregate function MAX to all of the affected rows of the table StudentProfiles.

CREATE TABLE StudentProfiles(StudentsName VARCHAR(125),
      StudentsYearInSchool SMALLINT, StudentsGPA DECIMAL(5,2))


CREATE TABLE CollegeBoundStudentsProfile       (YearInSchoolMin SMALLINT, YearInSchoolMax SMALLINT, StudentGPAMin       DECIMAL(5,2), StudentGPAMax DECIMAL(5,2))


CREATE TRIGGER UpdateCollegeBoundStudentsProfileTrigger AFTER UPDATE ON StudentProfiles REFERENCING NEW_TABLE AS ntable FOR EACH STATEMENT MODE DB2SQL 

BEGIN
  DECLARE maxStudentYearInSchool SMALLINT;
  SET maxStudentYearInSchool =
       (SELECT MAX(StudentsYearInSchool) FROM ntable);
  IF maxStudentYearInSchool >
       (SELECT MAX (YearInSchoolMax) FROM
          CollegeBoundStudentsProfile) THEN
    UPDATE CollegeBoundStudentsProfile SET YearInSchoolMax =
         maxStudentYearInSchool;
  END IF;

END

In the preceding example, the trigger is processed a single time following the processing of a triggering update statement because it is defined as a FOR EACH STATEMENT trigger. You will need to consider the processing overhead required by the database management system for populating the transition tables when you define a trigger that references transition tables.

 

Parent topic:

SQL triggers