IDS Forum

Re: Multiple Update Triggers Fires Multiple Times

Posted By: Luis Silvestre Marques
Date: Thursday, 9 June 2016, at 4:45 a.m.

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

IDS Forum is maintained by Administrator with WebBBS 5.12.