-- 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,
  ai VARCHAR,
  bu VARCHAR,
  au VARCHAR,
  bd VARCHAR,
  ad VARCHAR
);
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
-- initiated. When this trigger is initiated, 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 force the triggers to execute. The SELECT
-- statements 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;