DB2 Basics: Triggers

As with the other entries in my DB2 Basics series, this entry does not cover everything about triggers. Instead, I’m covering the basics and a few important points.

DB2 triggers are sometimes over-used and over-complicated. Basically, triggers are actions(SQL) that take place when a triggering action occurs.

Triggering Actions

For DB2, the valid triggering actions are on a table basis. For a specified table, the triggering action can be:

Insert
Update
Delete

Support for multi-action triggers was added in db2 9.7.4: https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQLTips4DB2LUW/entry/all_in_one_multi_action_triggers_in_db2_9_7_4376?lang=en

I haven’t had the chance to play with multi-action triggers yet.

Triggered Action Timing

The actions triggered can then take place BEFORE, AFTER, or INSTEAD OF the triggering action.

BEFORE simply means that the trigger’s actions will take place before the triggering action. Uses for this include calculating some value that is needed for the triggering action or checking constraints before taking an action. If you’re going to change the value of of some field being updated or inserted as a part of the triggering action, this is usually the way to do it.

AFTER means that the trigger’s actions will take place after the triggering action. This is usually used to populate other tables or to note in other tables that changes were made. In a WebSphere Commerce environment, both staging triggers and triggers for selective cache invalidation are AFTER triggers.

INSTEAD OF triggers, I’ve never actually used. My understanding is that their main application is in dealing with inserts to otherwise non-insertable views.

Trigger Granularity

Triggers can either fire with every row (FOR EACH ROW) or just once for a statement including a triggering action (FOR EACH STATEMENT). In practice, I’ve only actually used FOR EACH ROW. I think these are pretty self-explanatory. FOR EACH ROW means that the triggered action takes place once for each row affected by the triggering action. FOR EACH STATEMENT means that the triggered action takes place only once for each statement that includes a triggering action, reguardless of how many rows are affected.

Trigger Issues

Triggers are meant to be small pieces of SQL that run fast. Their execution must be complete for any transaction with the triggering action to complete. The time it takes a trigger to complete affects the total length of a transaction, so it is important to be aware of that for performance. Sometimes people try to put too much into a trigger. If you find yourself struggling with a trigger too much, it is reasonable to ask if the actions it is taking shouldn’t be coded into an application.

Triggers can also get into circular references, more than many areas of DB2. For example if you have a trigger that fires on update of table ‘SALES’, but then in turn updates the ‘SALES’ table, you may get an error about circular references.

It is important to test performance after adding triggers – in a few cases, I’ve seen poorly written triggers increase response time drastically.

Since triggers can contain multiple SQL statements which are themselves terminated with a semicolon, usually files of create trigger statements use an alternate terminatior. I’ve most frequently seen @ or # used. Because of this, they are usually executed with slightly different syntax. My normal go-to syntax for executing db2 SQL files is:

db2 -tvf filename >filename.out

For triggers (or stored procedures or other compound SQL), I use syntax more like this:

db2 -td@ -vf filename >filename.out

What, no Login Trigger?

Oracle has the concept of a login trigger. DB2 doesn’t specifically call it a trigger, but as of 9.7.3, there’s a parameter called CONNECT_PROC that can be set to the name of a stored procedure that is executed on connect. See Serge Rielau’s blog entry on this: https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQLTips4DB2LUW/entry/logon_triggers_in_db2_kind_of136?lang=en_us