triggergeneral.sql

derby database source code.good for you.

--
-- General trigger test
--

create function printTriggerInfo() returns varchar(1) PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL
  EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Triggers.printTriggerInfo';
create function triggerFiresMin(s varchar(128)) returns varchar(1) PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL
  EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Triggers.triggerFiresMinimal';
create function triggerFires(s varchar(128)) returns varchar(1) PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL
  EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Triggers.triggerFires';
create function begInvRefToTECTest() returns varchar(1) PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL
  EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Triggers.beginInvalidRefToTECTest';
create procedure notifyDMLDone() PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL
  EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Triggers.notifyDMLDone';

drop table x;
create table x (x int, y int, z int, constraint ck1 check (x > 0));
create view v as select * from x;

-- ok
create trigger t1 NO CASCADE before update of x,y on x for each row mode db2sql values 1;

-- trigger already exists
create trigger t1 NO CASCADE before update of x,y on x for each row mode db2sql values 1;
-- trigger already exists
create trigger app.t1 NO CASCADE before update of x,y on x for each row mode db2sql values 1;

-- make sure system tables look as we expect
select cast(triggername as char(10)), event, firingtime, type, state, referencedcolumns from sys.systriggers;

select cast(triggername as char(10)), CAST (TRIGGERDEFINITION AS VARCHAR(180)), STMTNAME from sys.systriggers t, sys.sysstatements s 
		where s.stmtid = t.actionstmtid;

select cast(triggername as char(10)), tablename from sys.systriggers t, sys.systables tb
		where t.tableid = tb.tableid;

values SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', 'SYSTRIGGERS');
drop trigger t1;

-- not in sys schema
create trigger sys.tr NO CASCADE before insert on x for each row mode db2sql values 1;

-- not on table in sys schema
create trigger tr NO CASCADE before insert on sys.systables for each row mode db2sql values 1;

-- duplicate columns, not allowed
create trigger tr NO CASCADE before update of x, x on x for each row mode db2sql values 1;

-- no params in column list
create trigger tr NO CASCADE before update of x, ? on x for each row mode db2sql values 1;

-- invalid column
create trigger tr NO CASCADE before update of doesnotexist on x for each row mode db2sql values 1;

-- not on view
create trigger tr NO CASCADE before insert on v for each row mode db2sql values 1;

-- error to use table qualifier
create trigger tr NO CASCADE before update of x.x on x for each row mode db2sql values 1;

-- error to use schema.table qualifier
create trigger tr NO CASCADE before update of app.x.x on x for each row mode db2sql values 1;

-- no params in trigger action
-- bad
create trigger tr NO CASCADE before delete on x for each row mode db2sql select * from x where x = ?;

create trigger stmttrigger NO CASCADE before delete on x for each statement mode db2sql values 1;
select triggername, type from sys.systriggers where triggername = 'STMTTRIGGER';
drop trigger stmttrigger;

create trigger rowtrigger NO CASCADE before delete on x for each row mode db2sql values 1;
select triggername, type from sys.systriggers where triggername = 'ROWTRIGGER';
drop trigger rowtrigger;

-- fool around with depedencies

-- CREATE TRIGGER
create trigger t2 NO CASCADE before update of x,y on x for each row mode db2sql values 1;

-- CREATE CONSTRAINT
alter table x add constraint ck2 check(x > 0);

-- DROP VIEW
drop view v;

-- CREATE VIEW
create view v as select * from x;

-- CREATE INDEX
create index ix on x(x);

-- DROP TRIGGER: to the other types we have here
drop trigger t2;

-- DROP INDEX
drop index ix; 

-- DROP CONSTRAINT
alter table x drop constraint ck2;

-- MAKE SURE TRIGGER SPS IS RECOMPILED IF TABLE IS ALTERED.
create table y (x int, y int, z int);

create trigger tins after insert on x referencing new_table as newtab for each statement mode db2sql insert into y select x, y, z from newtab;

insert into x values (1, 1, 1);
alter table x add column w int default 100;
alter table x add constraint nonulls check (w is not null);
insert into x values (2, 2, 2, 2);
select * from y;
drop trigger tins;
drop table y;

-- prove that by dropping the underlying table, we have dropped the trigger
-- first, lets create a few other triggers
create trigger t2 NO CASCADE before update of x,y on x for each row mode db2sql values 1;
create trigger t3 after update of x,y on x for each statement mode db2sql values 1;
create trigger t4 after delete on x for each statement mode db2sql values 1;
select cast(triggername as char(10)), tablename from sys.systriggers t, sys.systables  tb
		where t.tableid = tb.tableid order by 1;
drop view v;
drop table x;
select cast(triggername as char(10)), tablename from sys.systriggers t, sys.systables  tb
		where t.tableid = tb.tableid order by 1;

--
-- schema testing
--
create table x (x int, y int, z int);
create schema test;

create trigger test.t1 NO CASCADE before delete on x for each row mode db2sql values 1;
set schema test;

create trigger t2 NO CASCADE before delete on app.x for each row mode db2sql values 1;

select schemaname, triggername from sys.systriggers t, sys.sysschemas s
	where s.schemaid = t.schemaid;

set schema app;
-- fails
drop schema test restrict;

drop trigger test.t2;

-- fails
drop schema test restrict;

set schema test;
drop trigger t1;
set schema app;

-- ok this time
drop schema test restrict;

--
-- Test the information in the trigger information context
--
create table t (x int, y int, c char(1));
create trigger t1 NO CASCADE before insert on t for each statement mode db2sql
	values app.printTriggerInfo();
insert into t values (1,1,'1');

delete from t;
drop trigger t1;
create trigger t1 after insert on t for each statement mode db2sql
	values app.printTriggerInfo();
insert into t values (1,1,'1');

drop trigger t1;
create trigger t1 NO CASCADE before update on t for each statement mode db2sql
	values app.printTriggerInfo();
update t set x = 2;
update t set y = 2, c = '2';

drop trigger t1;
create trigger t1 after update on t for each statement mode db2sql
	values app.printTriggerInfo();
update t set x = 3;
update t set y = 3, c = '3';

drop trigger t1;
create trigger t1 no cascade before delete on t for each statement mode db2sql
	values app.printTriggerInfo();
delete from t;
drop trigger t1;

insert into t values(3,3,'3');
create trigger t1 after delete on t for each statement mode db2sql
	values app.printTriggerInfo();
delete from t;
drop trigger t1;

--
-- Test trigger firing order
--
create trigger t1 after insert on t for each row mode db2sql
	values app.triggerFiresMin('3rd');
create trigger t2 after insert on t for each statement mode db2sql
	values app.triggerFiresMin('1st');
create trigger t3 no cascade before insert on t for each row mode db2sql
	values app.triggerFiresMin('4th');
create trigger t4 after insert on t for each row mode db2sql
	values app.triggerFiresMin('2nd');
create trigger t5 no cascade before insert on t for each statement mode db2sql
	values app.triggerFiresMin('5th');
insert into t values (1,1,'1');
drop trigger t1;
drop trigger t2;
drop trigger t3;
drop trigger t4;
drop trigger t5;

-- try multiple values, make sure result sets don't get screwed up
-- this time we'll print out result sets
create trigger t1 after insert on t for each row mode db2sql
	values app.triggerFires('3rd');
create trigger t2 no cascade before insert on t for each statement mode db2sql
	values app.triggerFires('1st');
create trigger t3 after insert on t for each row mode db2sql
	values app.triggerFires('4th');
create trigger t4 no cascade before insert on t for each row mode db2sql
	values app.triggerFires('2nd');
create trigger t5 after insert on t for each statement mode db2sql
	values app.triggerFires('5th');
insert into t values 
	(2,2,'2'),
	(3,3,'3'),
	(4,4,'4');

delete from t;
drop trigger t1;
drop trigger t2;
drop trigger t3;
drop trigger t4;
drop trigger t5;

--
-- Test firing on empty change sets, 
-- statement triggers fire, row triggers
-- do not.
--
create trigger t1 after insert on t for each row mode db2sql
	values app.triggerFires('ROW: empty insert, should NOT fire');
create trigger t2 after insert on t for each statement mode db2sql
	values app.triggerFires('STATEMENT: empty insert, ok');
insert into t select * from t;
drop trigger t1;
drop trigger t2;

create trigger t1 after update on t for each row mode db2sql
	values app.triggerFires('ROW: empty update, should NOT fire');
create trigger t2 after update on t for each statement mode db2sql
	values app.triggerFires('STATEMENT: empty update, ok');
update t set x = x;
drop trigger t1;
drop trigger t2;

create trigger t1 after delete on t for each row mode db2sql
	values app.triggerFires('ROW: empty delete, should NOT fire');
create trigger t2 after delete on t for each statement mode db2sql
	values app.triggerFires('STATEMENT: empty delete, ok');
delete from t;
drop trigger t1;
drop trigger t2;
drop table x;


--
-- After alter table, should pick up the new columns
--
create table talt(c1 int);
create trigger tins after insert on talt for each statement mode db2sql
	values app.printTriggerInfo();
create trigger tdel no cascade before delete on talt for each row mode db2sql
	values app.printTriggerInfo();
create trigger tupd after update on talt for each statement mode db2sql
	values app.printTriggerInfo();
insert into talt values (1);
alter table talt add column cnew int default null;
select * from talt;

insert into talt values (2,2);
delete from talt;
insert into talt values (3,3);
update talt set cnew = 666;
drop trigger tins;
drop trigger tdel;
drop trigger tupd;

-- make sure update w/ columns doesn't pick up new col
create trigger tupd after update of c1 on talt for each statement mode db2sql
	values app.printTriggerInfo();
alter table talt add column cnew2 int default null;
insert into talt values (1,1,1);
update talt set cnew2 = 666;

-- clean up
drop table talt;


--