Multiple Update Triggers Fires Multiple Times
Posted in 2016
Hi,
We are running 11.70.FC7W2 on Solaris 11. We noticed some very unexpected
behavior.
I created a simple test case below.
When we have 2 update triggers of the same columns, each update trigger
gets fired multiple times when you update the columns listed in the
trigger. In this case, we have 3 columns in the trigger and the 2 triggers
get fired 3 times each.
(We also test with 2 columns in the update trigger and each trigger gets
fired twice)
Any ideas?
Thank You,
--David
--create debugging table
create table dbg ( rec_key serial,
trig_source varchar (30),
lmod datetime year to second
) ;
create table tab_1 ( rec_key serial,
fname varchar (30),
lname varchar (30),
state varchar (2)
) ;
--create first update trigger
create trigger trg_tab_1_u_1 update of fname, lname, state
on tab_1 REFERENCING OLD AS pre NEW AS post FOR EACH ROWwhen (pre.fname != post.fname or pre.lname != post.lname or pre.state !=
post.state)
(insert into dbg values (0, "trg_tab_1_u_1", current)) ;
--create second update trigger
create trigger trg_tab_1_u_2 update of fname, lname, state
on tab_1 REFERENCING OLD AS pre NEW AS post FOR EACH ROWwhen (pre.fname != post.fname or pre.lname != post.lname or pre.state !=
post.state)
(insert into dbg values (0, "trg_tab_1_u_2", current)) ;
insert into tab_1 values (0, "Mike", "Not Smith", "NY") ;
select * from tab_1 ;
update tab_1
set (fname, lname, state) = ("David", "Smith", "CA")
where rec_key = 1 ;
select * from dbg ;
Database selected.
> select * from dbg ;
rec_key trig_source lmod
1 trg_tab_1_u_2 2016-05-26 11:31:27
2 trg_tab_1_u_1 2016-05-26 11:31:27
3 trg_tab_1_u_2 2016-05-26 11:31:27
4 trg_tab_1_u_1 2016-05-26 11:31:27
5 trg_tab_1_u_2 2016-05-26 11:31:27
6 trg_tab_1_u_1 2016-05-26 11:31:27
6 row(s) retrieved.
--001a114b0e5294ee0b0533c09684