BEFORE SQL triggers

In the following example, the trigger is used to set the fiscal quarter for the corporation before inserting the row into the target table.

CREATE TABLE TransactionTable (DateOfTransaction DATE, FiscalQuarter SMALLINT)

CREATE TRIGGER TransactionBeforeTrigger BEFORE INSERT ON TransactionTable
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2ROW
BEGIN
  DECLARE newmonth SMALLINT;
SET newmonth = MONTH(new_row.DateOfTransaction);
  IF newmonth < 4 THEN
    SET new_row.FiscalQuarter=3;
  ELSEIF newmonth < 7 THEN
    SET new_row.FiscalQuarter=4;
  ELSEIF newmonth < 10 THEN
    SET new_row.FiscalQuarter=1;
  ELSE
    SET new_row.FiscalQuarter=2;
  END IF;
END

For the SQL insert statement below, the "FiscalQuarter" column is set to 2, if the current date is November 14, 2000.

INSERT INTO TransactionTable(DateOfTransaction)
           VALUES(CURRENT DATE) 

SQL triggers have access to and can use User-defined Distinct Types (UDTs) and stored procedures. In the following example, the SQL trigger calls a stored procedure to execute some predefined business logic, in this case, to set a column to a predefined value for the business.

CREATE DISTINCT TYPE enginesize AS DECIMAL(5,2) WITH COMPARISONS

CREATE DISTINCT TYPE engineclass AS VARCHAR(25) WITH COMPARISONS

CREATE PROCEDURE SetEngineClass(IN SizeInLiters enginesize,
                           OUT CLASS engineclass)
LANGUAGE SQL CONTAINS SQL
BEGIN
  IF SizeInLiters<2.0 THEN
    SET CLASS = 'Mouse';
  ELSEIF SizeInLiters<3.1 THEN
    SET CLASS ='Economy Class';
  ELSEIF SizeInLiters<4.0 THEN
    SET CLASS ='Most Common Class';
  ELSEIF SizeInLiters<4.6 THEN
    SET CLASS = 'Getting Expensive';
  ELSE
    SET CLASS ='Stop Often for Fillups';
  END IF;
END

CREATE TABLE EngineRatings (VariousSizes enginesize, ClassRating engineclass)

CREATE TRIGGER SetEngineClassTrigger BEFORE INSERT ON EngineRatings
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2ROW
  CALL SetEngineClass(new_row.VariousSizes, new_row.ClassRating)

For the SQL insert statement below, the "ClassRating" column is set to "Economy Class", if the "VariousSizes" column has the value of 3.0.

INSERT INTO EngineRatings(VariousSizes) VALUES(3.0)

SQL requires all tables, user-defined functions, procedures and user-defined types to exist before creating an SQL trigger. In the examples above, all of the tables, stored procedures, and user-defined types are defined before the trigger is created.