SQL Guide : SQL extensions : Triggers : Trigger example
  
Trigger example
Trigger example
This example shows how simple triggers work. It contains some triggers that work correctly and some triggers that contain errors. For the successful triggers in the example, a table (named trigger_test) is created and six triggers are created on that table. Each trigger, when fired, inserts a record into another table (named trigger_output). After performing the DML statements (INSERT, UPDATE, and DELETE) that fire the triggers, the results of the triggers are displayed by selecting all records from the trigger_output table.
DROP TABLE TRIGGER_TEST;
DROP TABLE TRIGGER_ERR_TEST;
DROP TABLE TRIGGER_ERR_B_TEST;
DROP TABLE TRIGGER_ERR_A_TEST;
DROP TABLE TRIGGER_OUTPUT;
COMMIT WORK;
-- Create a table that has a column for each of the possible trigger
-- types (for example, BI = a trigger that is on Insert
-- operations and that executes as a "Before" trigger).
CREATE TABLE TRIGGER_TEST(
  XX VARCHAR,
  BI VARCHAR, -- BI = Before Insert
  AI VARCHAR, -- AI = After Insert
  BU VARCHAR, -- BU = Before Update
  AU VARCHAR, -- AU = After Update
  BD VARCHAR, -- BD = Before Delete
  AD VARCHAR -- AD = After Delete );
COMMIT WORK;

-- Table for 'before' trigger errors
CREATE TABLE TRIGGER_ERR_B_TEST(
  XX VARCHAR,
  BI VARCHAR,
  AI VARCHAR,
  BU VARCHAR,
  AU VARCHAR,
  BD VARCHAR,
  AD VARCHAR
);
INSERT INTO TRIGGER_ERR_B_TEST VALUES('x','x','x','x','x','x','x');
COMMIT WORK;

-- Table for 'after X' trigger errors
CREATE TABLE TRIGGER_ERR_A_TEST(
  XX VARCHAR,
  BI VARCHAR, -- Before Insert
  AI VARCHAR, -- After Insert
  BU VARCHAR, -- Before Update
  AU VARCHAR, -- After Update
  BD VARCHAR, -- Before Delete
  AD VARCHAR -- After Delete
);
INSERT INTO TRIGGER_ERR_A_TEST VALUES('x','x','x','x','x',
'x','x');
COMMIT WORK;

CREATE TABLE TRIGGER_OUTPUT( TEXT VARCHAR, NAME VARCHAR, SCHEMA VARCHAR
);
COMMIT WORK;
-- Successful triggers
-- Create a "Before" trigger on insert operations. When a record is
-- inserted into the table named trigger_test, then this trigger is
-- fired. When this trigger is fired, it inserts a record into the
-- "trigger_output" table to show that the trigger actually executed.
"CREATE TRIGGER TRIGGER_BI ON TRIGGER_TEST
  BEFORE INSERT
  REFERENCING NEW BI AS NEW_BI
BEGIN
  EXEC SQL PREPARE BI INSERT INTO TRIGGER_OUTPUT VALUES(
      'BI', TRIG_NAME(0), TRIG_SCHEMA(0));
  EXEC SQL EXECUTE BI;
  SET NEW_BI = 'TRIGGER_BI';
END";

COMMIT WORK;
"CREATE TRIGGER TRIGGER_AI ON TRIGGER_TEST
  AFTER INSERT
  REFERENCING NEW AI AS NEW_AI
BEGIN
  EXEC SQL PREPARE AI INSERT INTO TRIGGER_OUTPUT VALUES( 'AI',   TRIG_NAME(0), TRIG_SCHEMA(0));
EXEC SQL EXECUTE AI;

SET NEW_AI = 'TRIGGER_AI'; END";
COMMIT WORK;

"CREATE TRIGGER TRIGGER_BU ON TRIGGER_TEST
  BEFORE UPDATE
  REFERENCING NEW BU AS NEW_BU BEGIN
  EXEC SQL PREPARE BU INSERT INTO TRIGGER_OUTPUT VALUES( 'BU',
TRIG_NAME(0), TRIG_SCHEMA(0));
EXEC SQL EXECUTE BU;
SET NEW_BU = 'TRIGGER_BU'; END"; COMMIT WORK;
"CREATE TRIGGER TRIGGER_AU ON TRIGGER_TEST
AFTER UPDATE
  REFERENCING NEW AU AS NEW_AU BEGIN
  EXEC SQL PREPARE AU INSERT INTO TRIGGER_OUTPUT VALUES(
    'AU', TRIG_NAME(0), TRIG_SCHEMA(0));
  EXEC SQL EXECUTE AU;
  SET NEW_AU = 'TRIGGER_AU';
END";
COMMIT WORK;
"CREATE TRIGGER TRIGGER_BD ON TRIGGER_TEST
  BEFORE DELETE
  REFERENCING OLD BD AS OLD_BD BEGIN
  EXEC SQL PREPARE BD INSERT INTO TRIGGER_OUTPUT VALUES(
    'BD', TRIG_NAME(0), TRIG_SCHEMA(0));
EXEC SQL EXECUTE BD;
SET OLD_BD = 'TRIGGER_BD'; END"; COMMIT WORK;
"CREATE TRIGGER TRIGGER_AD ON TRIGGER_TEST
AFTER DELETE
REFERENCING OLD AD AS OLD_AD BEGIN
EXEC SQL PREPARE AD INSERT INTO TRIGGER_OUTPUT VALUES( 'AD', TRIG_NAME(0), TRIG_SCHEMA(0));
EXEC SQL EXECUTE AD;
SET OLD_AD = 'TRIGGER_AD'; END";
COMMIT WORK;
-- This attempt to create a trigger will fail. The statement
-- specifies the wrong data type for the error variable named
-- ERRSTR.
"CREATE TRIGGER TRIGGER_ERR_AU ON TRIGGER_ERR_A_TEST AFTER UPDATE
REFERENCING NEW AU AS NEW_AU BEGIN
-- The following line is incorrect; ERRSTR must be declared
-- as VARCHAR, not INTEGER;
DECLARE ERRSTR INTEGER; -- ...
RETURN SQLERROR ERRSTR;
END";
COMMIT WORK;
-- Trigger that returns an error message.
"CREATE TRIGGER TRIGGER_ERR_BI ON TRIGGER_ERR_B_TEST
  BEFORE INSERT
  REFERENCING NEW BI AS NEW_BI
BEGIN
-- ...
  RETURN SQLERROR 'Error in TRIGGER_ERR_BI';
END";
COMMIT WORK;
-- Success trigger tests. These Insert, Update, and Delete
-- statements will force the triggers to fire. The SELECT
-- statements will show you the records in the trigger_test and
-- trigger_output tables.
INSERT INTO TRIGGER_TEST(XX) VALUES ('XX');
COMMIT WORK;
-- Show the records that were inserted into the trigger_test
-- table. (The records for trigger_output are shown later.)
SELECT * FROM TRIGGER_TEST; COMMIT WORK;
UPDATE TRIGGER_TEST SET XX = 'XX updated'; COMMIT WORK;
-- Show the records that were inserted into the trigger_test
-- table. (The records for trigger_output are shown later.)
SELECT * FROM TRIGGER_TEST; COMMIT WORK;
DELETE FROM TRIGGER_TEST; COMMIT WORK;
SELECT * FROM TRIGGER_TEST;
-- Show that the triggers did run and did add values to the -- trigger_output table. You should see 6 records one for -- each of the triggers that executed. The 6 triggers are: -- BI, AI, BU, AU, BD, AD.
SELECT * FROM TRIGGER_OUTPUT;
COMMIT WORK;
-- Error trigger test
INSERT INTO TRIGGER_ERR_B_TEST(XX) VALUES ('XX');
COMMIT WORK;
See also
Triggers