OVO OVO - 4 months ago 10
SQL Question

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.

Answer

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.