DB2 trigger

Paulo Pereira

DB2 trigger

I need to know how can I get examples of triggers to update a different
table from an update on another one. 

I have a table called PPSTA which have an atribute called LCR. Another
table called HIST has the same attribute. Also sometimes the PPSTA table
will have a mass update to which need to replicate on HIST the same way.


What I need is: Every time that LCR were updated in PPSTA I need to
replicate this change on HIST table using TRIGGER. 

Is there how to do this ? Please give me examples 

Maybe something like this (I am ot sure): 

CREATE TRIGGER PU_UPD_PPSTA
AFTER UPDATE OF LCR ON PPSTA 
REFERENCING OLD AS O NEW AS N
FOR EACH ROW 
MODE DB2SQL 
BEGIN ATOMIC 
 UPDATE HIST SET LCR.HIST = LCR.PPSTA 
END;

Suresh Sane

DB2 trigger
(in response to Paulo Pereira)
Paulo,


I think the only correction you need is changing:


UPDATE HIST SET LCR.HIST = LCR.PPSTA


to


UPDATE HIST SET LCR = N.LCR
WHERE keycolumn = N.keycolumn


Also, keep in mind that you need to block direct inserts/updates to the HIST table where the values are different. With that, you will need another update trigger on HIST (as well as for insert if inserts are permitted), which will signal an error when N.LCR is not same.


Here is an example for tables CAS4DEPT and CAS4EMPL where updates are blocked: (pasted from one of my earlier presentations)


CREATE TRIGGER CAS4TR04 NO CASCADE

BEFORE UPDATE OF DEPTNAME ON TRNTRT.CAS4EMPL

REFERENCING OLD AS O

NEW AS N

FOR EACH ROW MODE DB2SQL

WHEN (N.DEPTNAME <>

(SELECT DEPTNAME

FROM TRNTRT.CAS4DEPT CAS4DEPT

WHERE CAS4DEPT.DEPTID = O.DEPTID

))

BEGIN ATOMIC

SIGNAL SQLSTATE ‘7TR04‘

('CAS4TR04 DEPT NAME UPDATE NOTALLOWED');

END#


Contact me offline if you need further help.


Thx

Suresh


________________________________
From: Paulo Pereira <[login to unmask email]>
Sent: Friday, October 13, 2017 10:44 PM
To: [login to unmask email]
Subject: [IDUGNA] - DB2 trigger


I need to know how can I get examples of triggers to update a different
table from an update on another one.

I have a table called PPSTA which have an atribute called LCR. Another
table called HIST has the same attribute. Also sometimes the PPSTA table
will have a mass update to which need to replicate on HIST the same way.


What I need is: Every time that LCR were updated in PPSTA I need to
replicate this change on HIST table using TRIGGER.

Is there how to do this ? Please give me examples

Maybe something like this (I am ot sure):

CREATE TRIGGER PU_UPD_PPSTA
AFTER UPDATE OF LCR ON PPSTA
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
UPDATE HIST SET LCR.HIST = LCR.PPSTA
END;

-----End Original Message-----