BEFORE SQL triggers

 

BEFORE triggers cannot change tables, but they can be used to verify input column values and to change column values that are inserted or updated in a table.

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 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.

 

Parent topic:

SQL triggers