Re: Multiple Update Triggers Fires Multiple Times

I am having some problems posting to the forum. Sorry about the spam, but forum seems to be broken for me. According to the manual: Whether it updates one column or more than one column from the column list, a triggering UPDATE statement activates each Update trigger only once. So the behavior described by the OP seem to be wrong. I tried the the example provided in 12.10FC6DE and got the same results. If i merge the 2 triggers, using multiple WHEN blocks on a single trigger, it only executes the trigger actions 1 time. You should open a PMR with IBM support so they check into it. As a workaround, you can try merging the triggers, using multiple when blocks. I was replying to the following: | 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 ROW | when (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 ROW | when (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. ---- Luis Marques