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
