Hi,

I am converting potion of the Informix database to UDB on AIX server
along with triggers.

The Informix trigger for update function has been coded as follows,

create trigger "informix".upd_altered_state update on "locator"
.altered_state referencing old as pre new as post
for each row
(
insert into "informix".s_altered_state (cnt,dt,action,
state,adjoining) values (0 ,CURRENT year to fraction(3) ,'D'
,pre.state
,pre.adjoining ),
insert into "informix".s_altered_state (cnt,dt,action,
state,adjoining) values (0 ,CURRENT year to fraction(3) ,'I'
,post.state
,post.adjoining ));


In DB2 I have not found way to create one trigger be able to insert two
rows so I split the update trigger to two triggers.

create trigger upd_altered_state1 after update on
altered_state referencing old as pre
for each row
insert into s_altered_state (dt,action,
state,adjoining) values (CURRENT timestamp,'D' ,pre.state
,pre.adjoining );

create trigger upd_altered_state2 after update on
altered_state referencing new as post
for each row
insert into s_altered_state (dt,action,
state,adjoining) values (CURRENT timestamp,'I' ,post.state
,post.adjoining );



I have no problem to generate two transaction records in s_altered_state
table when there is an update on altered_state table but at the later
time when we try to update primary table in Informix using
s_altered_state transaction table then we are running into problem
sometimes. The 'D' transaction and 'I' transaction are out of order.

My questions are 1) is there any way I can code one update trigger which
will insert two rows into s_altered_state table? 2) If I have to code
two triggers in DB2 then how do I control it always inserts 'D'
(delete) transaction record before 'I' (insert) transaction record. If
we had 'I' record before 'D' record, we will get duplicate issue and
will skip insert 'I' record that is our current problem.








Thank you and have a nice day,

Lisa Ley
Database Administrator, Production Support
(301)380-3810 (office)
(240)372-5328 (cell)

This communication contains information from Marriott International,
Inc. that may be confidential. Except for personal use by the intended
recipient, or as expressly authorized by the sender, any person who
receives this information is prohibited from disclosing, copying,
distributing, and/or using it. If you have received this communication
in error, please immediately delete it and all copies, and promptly
notify the sender. Nothing in this communication is intended to operate
as an electronic signature under applicable law






DB2 & UDB email list listserv db2-l LazyDBA home page