Triggers in DB2 Express-C 9.7.4

My team at IBM recently released DB2 Express-C 9.7.4, the latest and greatest version of our free database.

Raul wrote up a detailed article with the technical nitty-gritty of what’s new. There’s a bunch of different improvements, but one thing that’s caught my eye are the enhancements to triggers.

A trigger is something defined to fire automatically when you insert, update, or delete a row in a table. Starting with 9.7.4, you can basically inline a whole stored procedure in the trigger definition. This is nice because it lets you keep the code for all the different actions on a table together.

Let me quote Raul’s example:

CREATE TABLE COMPANY_STATS (NBEMP INTEGER)
!

CREATE TRIGGER HIRED
 AFTER INSERT OR DELETE OR UPDATE OF SALARY ON EMPLOYEE
 REFERENCING NEW AS N OLD AS O FOR EACH ROW
   BEGIN
         IF INSERTING
         THEN UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;
         END IF;

         IF DELETING
         THEN UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1;
         END IF;

         IF (UPDATING AND (N.SALARY > 1.1 * O.SALARY))
         THEN SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%';
         END IF;
   END
!

Ignore that last part. All salary increases should be > 10%.