Porting multi-action triggers from Oracle to DB2 9.7

A trigger is a database object, which does a set of actions in response to a DML operation, such as insert, update, and delete, against a specific table. DB2 9.7 supports two types of triggers in PL/SQL; they are row-level triggers and statement-level triggers. A row-level trigger fires for every single row-level change, and a statement-level trigger fires for every single statement, even though one statement affects multiple rows in the table.

Triggers are being supported even by Oracle with an exception, that is, Oracle allows a single trigger to handle multiple actions. These are known as multi-action triggers, which were not being supported by earlier versions of DB2, but DB2 9.7 supports it.

Getting ready

Let's start looking at the multi-action trigger, and create some base tables to work on.

How to do it...

We will create a sample trigger on the employee table. Whenever there are any changes made to the employee table, it will log...