Creating trigger for DB2

Recently I was solving a problem, where a trigger for DB2 database was needed. Using few of available tutorials on the internet, I started to build my first DB2 trigger. Everything seemed to be easy understand, until my first test of the trigger.

After couple minutes of googling I have eventually discovered what did the mysterious error in the console mean. The problem was in termination character for the SQL query. As the query, which should be ran after triggering event has to be terminated with a semicolon, the trigger query itself has to be terminated with something else apparently - so that the SQL engine won't start the execution after first semicolon it sees after the action query.

Solution

The solution is quiet simple - change the termination character.

--#SET TERMINATOR @

CREATE TRIGGER trg1 BEFORE INSERT ON table

FOR EACH ROW

BEGIN ATOMIC

VALUES 1; --here you can see the semicolon, which normally causes problems :)

END

@

--SET TERMINATOR ;

Some usefull links:

• DB2 trigger basics
• IBM DB2 trigger documentation

If you are not satisfied with the trigger you have created, you will probably think about deleting (dropping). This easy query will drop the created trigger:

DROP TRIGGER trigger_name;

PDF File

An Introduction to Trigger in DB2 for OS