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;