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

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

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@

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

1 Answers