Create History Trigger Db2

0
votes

I am trying to create a trigger that copys a newly INSERTED and/or UPDATED record from one table to a History version of this table. The purpose is to keep a record every time a record in a table is inserted and/or updated.

This is the Create Trigger statement syntax that I have, following the IBM Db2 Trigger site.

CREATE TRIGGER "TADB2"."HIST_CSEMASTER_Z_LOG"
AFTER UPDATE ON TADB2.CSEMASTER_Z
REFERENCING NEW AS N
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO TADB2.HIST_CSEMASTER_Z
values (N.CSEMASTERID, N.CURRID, N.SUBCURRID, N.EXPIREDATE, N.CREATEDATE, N.CUSTVIEWNUM, N.CSEMSTRNAME, N.JOINCODE, N.CONTACTCOUNTRYCD, N.CONTACTSERIALNUM, N.AUDIENCECD, N.SPONSORNAME, N.AVAILABLEDATE, N.OWNERCOUNTRYCD, N.OWNERSERIALNUM, N.PRMWWCHAPTERID, N.PRMWWSUBCHAPTERID, N.SECWWCHAPTERID, N.SECWWSUBCHAPTERID, N.INTRANETIND, N.TINSERTTS, N.TLASTUPDTS, N.IBM_TRADEMARK, N.OTHER_TRADEMARK, N.INTERNETIND, N.BRANDID, N.BUSINESSID, N.LIFECYCLE_STATUS);
END;

The error I am receiving is:

[Code: -104, SQL State: 42601] An unexpected token "END-OF-STATEMENT" was found following "N.LIFECYCLE_STATUS)". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.72.44

I've tried:

  • Removing the END
  • Removing the semi-colon after the END
  • Removing the semi-colon after the N.LIFECYCLE_STATUS);

Any help would be great! Thank you.

2
db2
Possible duplicate of An unexpected token "CREATE TRIGGER - mustaccio

2 Answers

1
votes

I highly recommend Db2 Temporal Tables for that. If you just use System Temporal functionality it would provide already the functionality you describe.

Worth to check it out

0
votes

You must use different from ';' statement terminator in your tool. Below is an example for DB2 Command Line Processor, which understands the first line as a directive to do this.

--#SET TERMINATOR @
CREATE TRIGGER "TADB2"."HIST_CSEMASTER_Z_LOG"
AFTER UPDATE ON TADB2.CSEMASTER_Z
REFERENCING NEW AS N
FOR EACH ROW
BEGIN ATOMIC
  INSERT INTO TADB2.HIST_CSEMASTER_Z
  values (N.CSEMASTERID, N.CURRID, N.SUBCURRID, N.EXPIREDATE, N.CREATEDATE, N.CUSTVIEWNUM, N.CSEMSTRNAME, N.JOINCODE, N.CONTACTCOUNTRYCD, N.CONTACTSERIALNUM, N.AUDIENCECD, N.SPONSORNAME, N.AVAILABLEDATE, N.OWNERCOUNTRYCD, N.OWNERSERIALNUM, N.PRMWWCHAPTERID, N.PRMWWSUBCHAPTERID, N.SECWWCHAPTERID, N.SECWWSUBCHAPTERID, N.INTRANETIND, N.TINSERTTS, N.TLASTUPDTS, N.IBM_TRADEMARK, N.OTHER_TRADEMARK, N.INTERNETIND, N.BRANDID, N.BUSINESSID, N.LIFECYCLE_STATUS);
END@