db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject REFERENCING clause in CREATE TRIGGER statement
Date Thu, 04 Feb 2010 05:15:46 GMT
Hi,

Wanted to confirm the behavior I am noticing with trigger action
attempting to use old/new columns from the triggering table. Without
the REFERENCING clause, trigger action can't access triggering table's
columns. The CREATE TRIGGER sql will fail in that case. I have
following ij script showing that behavior. Can someone familiar with
triggers comment if what I am trying is expected behavior?


java -Dij.exceptionTrace=true org.apache.derby.tools.ij
connect 'jdbc:derby:testDB;create=true';
create table test6table1 (id int, col1 int, col2 int);
insert into test6table1 values (1,1);
create table test6table2 (id int, col2_old int, col2_new int);
insert into test6table2 values (1,0,0);
--following create trigger fails because there is no referencing clause
--and trigger action is trying to reference columns from triggering table
create trigger test6tr1 after update of col1 on test6table1
	for each row
	update test6table2 set test6table2.col2_old=old.col2
	where id=1;
-- referencing is mandatory to access the triggering table's values
create trigger test6tr1 after update of col1 on test6table1
	referencing old as old
	for each row
	update test6table2 set test6table2.col2_old=old.col2
	where id=1;

thanks,
Mamta

Mime
View raw message