How to fix DB2 Error "An unexpected token CREATE OR REPLACE TRIGGER was found following "BEGIN-OF-STATEMENT"

Question

I am trying to create a db2 trigger so that a transaction history is maintained in a separate table. But I am getting below error and unable to proceed.

SQL Error [42601]: An unexpected token "CREATE TRIGGER HISTORY_TRIGGER AFTER" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER= 4.22.29

I am executing the sql through dbeaver.

I have already tried An unexpected token "CREATE TRIGGER and it doesn't help.

CREATE TRIGGER HISTORY_TRIGGER AFTER INSERT OR UPDATE ON MY_SCHEMA.TRANSACTIONS_TABLE
REFERENCING OLD ROW AS OLD
            NEW ROW AS NEW
BEGIN
    INSERT INTO MY_SCHEMA.HISTORY_TABLE
    (ID, EMAIL, REASON, ROLE, TIMESTAMP)
    VALUES(NEW.ID, NEW.EMAIL, NEW.REASON, NEW.ROLE, CURRENT_TIMESTAMP);
END

Please help me to find out what is wrong with my sql trigger.

@mustaccio as I have mentioned in the question itself i have already tried that. But seems like I had to change the default delimiter in dbeaver like Mark has mentioned below. — Leni
– Leni, Commented Dec 29, 2018 at 15:34

Mark Barinstein · Accepted Answer · 2018-12-28 08:36:50Z

2

Window->Preferences->General->Editors->SQL Editor->SQL Processing->Statement delimiter: @

CREATE OR REPLACE TRIGGER HISTORY_TRIGGER 
AFTER INSERT OR UPDATE ON MY_SCHEMA.TRANSACTIONS_TABLE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
    INSERT INTO MY_SCHEMA.HISTORY_TABLE (ID, EMAIL, REASON, ROLE, TIMESTAMP)
    VALUES(NEW.ID, NEW.EMAIL, NEW.REASON, NEW.ROLE, CURRENT_TIMESTAMP);
END@

answered Dec 28, 2018 at 8:36

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

danny117 Over a year ago

Before someone says to insert the old values into history. I want to say Its much easier to query history knowing the last value is the most recent in the table that is being tracked.

Collectives™ on Stack Overflow

How to fix DB2 Error "An unexpected token CREATE OR REPLACE TRIGGER was found following "BEGIN-OF-STATEMENT"

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related