DB2 – Multi Event Trigger Syntax Guide

auditdb2

I have a db2 LUW 10.5 database.

I want to create a trigger that saves a copy of a record into an audit table whenever, a record gets updated and inserted.

I came up with follwing syntax:

CREATE TRIGGER TR_TRANSACTION_MESSAGE_CREATE_HIST
AFTER INSERT OR UPDATE ON TRANSACTION_MESSAGE REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC
    INSERT INTO ....
END@

I get following error message when creating the trigger:

An unexpected token "OR UPDATE" was found following "". Expected
tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.16.53

However, if I create it as an insert only trigger i can create the trigger succeesful. The same is true when creating an update only trigger.

Any idea what is wrong with the syntax?

Best Answer

According to the manual, "If multiple events are specified, the triggered action must be a compound SQL (compiled) statement (SQLSTATE 42601)".

BEGIN ATOMIC indicates an inlined compound SQL statement. You'll have to replace it with BEGIN to make it compiled.

It might be worth noting the difference between the inlined and compound SQL in case of a trigger. An inlined compound statement becomes part of the triggering statement execution plan, while the compiled compound statement is invoked much like a stored procedure would be, with the associated overhead. Subsequently, triggers with inlined statements might perform a bit better, which may become noticeable on a heavily loaded system.

Related Question