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;