triggerrefclause.out

derby database source code.good for you.

insert into x values 1,3,4,5,6,9,666,667,668;
9 rows inserted/updated/deleted
ij> update x set x = x+1;
9 rows inserted/updated/deleted
ij> select * from x;
X          
-----------
2          
4          
5          
6          
7          
10         
667        
668        
669        
ij> select * from removed;
X          
-----------
ij> drop table x;
0 rows inserted/updated/deleted
ij> drop table removed;
0 rows inserted/updated/deleted
ij> commit;
ij> create table x (x int, y int);
0 rows inserted/updated/deleted
ij> create table y (x int, y int);
0 rows inserted/updated/deleted
ij> create trigger t1 after insert on x referencing new_table as newtab for each statement mode db2sql
	insert into y select newtab.x, y+newtab.y from newtab;
0 rows inserted/updated/deleted
ij> insert into x values (1,1);
1 row inserted/updated/deleted
ij> select * from y;
X          |Y          
-----------------------
1          |2          
ij> delete from y;
1 row inserted/updated/deleted
ij> drop trigger t1;
0 rows inserted/updated/deleted
ij> -- how about a correlation of a transition variable
create trigger t1 after insert on x referencing new_table as newtab for each statement mode db2sql
	insert into y select newtab2.x, y+newtab2.y from newtab newtab2;
0 rows inserted/updated/deleted
ij> insert into x values (1,1);
1 row inserted/updated/deleted
ij> select * from y;
X          |Y          
-----------------------
1          |2          
ij> -- lets prove that we are getting object types from row transition
-- variables.  this is only an issue with row triggers because
-- they are doing some funky stuff under the covers to make
-- a column appear just like a normal table column
drop table x;
0 rows inserted/updated/deleted
ij> drop table y;
0 rows inserted/updated/deleted
ij> create table val (x int);
0 rows inserted/updated/deleted
ij> create table x (b char(5) FOR BIT DATA);
0 rows inserted/updated/deleted
ij> create table y (b char(5) FOR BIT DATA);
0 rows inserted/updated/deleted
ij> create trigger t1 after insert on x referencing new as new for each row mode db2sql insert into y values (new.b || X'80');
0 rows inserted/updated/deleted
ij> insert into x values (X'E0');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA 'e02020202080' to length 5.
ij> select * from y;
B         
----------
ij> drop trigger t1;
0 rows inserted/updated/deleted
ij> create trigger t1 after insert on x referencing new as new for each row mode db2sql insert into y values new.b;
0 rows inserted/updated/deleted
ij> insert into x values null;
1 row inserted/updated/deleted
ij> select * from y;
B         
----------
NULL      
ij> drop trigger t1;
0 rows inserted/updated/deleted
ij> create trigger t1 after insert on x referencing new as new for each row mode db2sql insert into val values length(new.b);
0 rows inserted/updated/deleted
ij> insert into x values X'FFE0';
1 row inserted/updated/deleted
ij> select * from val;
X          
-----------
5          
ij> drop table x;
0 rows inserted/updated/deleted
ij> drop table y;
0 rows inserted/updated/deleted
ij> drop table val;
0 rows inserted/updated/deleted
ij> create table x (x dec(7,3));
0 rows inserted/updated/deleted
ij> create table y (x dec(8,4));
0 rows inserted/updated/deleted
ij> insert into x values 1234.1234, null, 1234.123;
3 rows inserted/updated/deleted
ij> select * from x;
X         
----------
1234.123  
NULL      
1234.123  
ij> select * from y;
X          
-----------
ij> create table t1 (col1 int not null primary key, col2 char(20));
0 rows inserted/updated/deleted
ij> create table s_t1(col1 int not null primary key, chgType char(20));
0 rows inserted/updated/deleted
ij> -- should work
create trigger trig_delete_2 after delete on t1 referencing OLD_TABLE as OLD for each statement mode db2sql 
	insert into s_t1 (select col1, 'D'
	from OLD  where OLD.col1 <> ALL
	(select col1 from s_t1 where  OLD.col1 = s_t1.col1));
0 rows inserted/updated/deleted
ij> drop trigger trig_delete_2;
0 rows inserted/updated/deleted
ij> -- should work
create trigger trig_delete_2 after delete on t1 referencing old_table as OLD for each statement mode db2sql 
	insert into s_t1 (select col1, 'D'
	from OLD where OLD.col1 <> ALL
	(select s_t1.col1 from s_t1, OLD where  OLD.col1 = s_t1.col1));
0 rows inserted/updated/deleted
ij> insert into t1 values (5, 'first row'), (3, 'second row'), (9, 'third row'),
			(4, 'forth row');
4 rows inserted/updated/deleted
ij> select * from s_t1;
COL1       |CHGTYPE             
--------------------------------
ij> delete from t1 where col1 = 3 or col1 = 9;
2 rows inserted/updated/deleted
ij> select * from s_t1;
COL1       |CHGTYPE             
--------------------------------
3          |D                   
9          |D                   
ij> insert into t1 values (9, 'third row'), (3, 'second row'), (7, 'fifth row');
3 rows inserted/updated/deleted
ij> delete from t1 where col1 = 3 or col1 = 7;
2 rows inserted/updated/deleted
ij> select * from s_t1;
COL1       |CHGTYPE             
--------------------------------
3          |D                   
9          |D                   
7          |D                   
ij> delete from t1;
3 rows inserted/updated/deleted
ij> select * from s_t1;
COL1       |CHGTYPE             
--------------------------------
3          |D                   
9          |D                   
7          |D                   
5          |D                   
4          |D                   
ij> rollback;
ij>