Hi,

I need some help...I'm fairly new to triggers and am attempting to
duplicate a trigger in DB2 that already works in SQL Server. The
basics are that I want to insert a row into an audit table using data
currently in a table BEFORE an update is executed. In addition, I need
to make sure the the record being updated is of a certain status so I
need to do a count on third table to ensure this status.
From what I've read, I can't do an insert in a BEFORE UPDATE trigger,
but I don't know where to go from here. I also have to do the same on
a BEFORE DELETE. Any help would be greatly appreciated.

My trigger looks like this:

CREATE TRIGGER AUDIT_UPD_RBRT1
NO CASCADE BEFORE UPDATE ON RB.RT1
REFERENCING OLD AS OAUDIT
FOR EACH ROW MODE DB2SQL
WHEN ( (SELECT COUNT(*) FROM RB.RT_FLNG_ADPTN
WHERE (IDL = OAUDIT.IDL AND
IDS = OAUDIT.IDS AND
STATUS_INDC = 'X')) = 1)
INSERT INTO RB.RB_AUDIT_RT_DATA
VALUES (OAUDIT.IDS,
OAUDIT.IDL,
CURRENT TIMESTAMP,
1,
11,
OAUDIT.EXACT,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
OAUDIT.RATE,
'TESTING');


Thanks,
Cindy

Hi Cindy,

To insert into the audit table you need to us an AFTER trigger, just as you
do in SQL Server.
(SQL server does not support BEFORE triggers)
BEFORE triggers are efficient to do checking and for generating extra
values.
I.e. if you want to raise an error if the row does not comply with some
rule, or you want to fill in soem data not provided by the SQL statement,
they are the way to go.
For example what you can do, is to use a before trigger for the checking and
error handling and an after trigger for the auditing.
W.r.t FOR EACH ROW vs FOR EACH STATEMENT:
If you have an SQL Server (statement) trigger that joins the DELETED and
INSERTED transition tables on the primary key to match them up, you probbaly
want to turn it into a FOR EACH ROW trigger which is more efficient since
teh NEW and OLD transition rows are already matched.

Cheers
Serge

Thank you very much Serge...I'll try all your suggestions.

Cindy