CODEWITHSUNDEEP
sqltriggersdb2
OVO
OVO

Reputation: 123

Writing a trigger for AFER Update on DB2

I want to set the column EDI_INVOICE to False whenever the DOCUMENT_TYPE is a REBILL for only specific BILL_TO_CODES

This is how I wrote my trigger 

CREATE TRIGGER BT_CU_CASS_REBILLS
AFTER UPDATE OF DOCUMENT_TYPE ON TLORDER 
REFERENCING OLD AS O NEW AS N
FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC 
     IF N.DOCUMENT_TYPE <> O.DOCUMENT_TYPE AND N.DOCUMENT_TYPE = 'REBILL' 
     AND N.BILL_TO_CODE IN (SELECT TRADING_PARTNER FROM EDI_PROFILES WHERE EDI_TYPE = '210' AND EDI_ACTIVE = 'True' AND UPPER(FTP_ADDRESS) LIKE '%CASS%')
     THEN SET N.EDI_INVOICE = 'True';
     END IF; 
     END

But when trying to execute the Query to create the trigger I get this Error 

SQL0797N The Trigger Lynx.BT_CU_CASS_REBILLS is defined with an unsporrted triggered SQL statement. Line Number = 11. SQLSTATE = 42987

Thank you in advance.

Upvotes: 0

Views: 102

Answers (1)

Charles
Charles

Reputation: 23783

If you want the trigger to change the data in the row for which the trigger is firing, ie.

THEN SET N.EDI_INVOICE = 'True';

you need to use a BEFORE UPDATE trigger.

Upvotes: 3

Related Questions