In DB2 version 6 triggers finally became available for DB2 tables. Many installations are now migrating to DB2 version 8 so it is about time to give you a litte information about triggers. This tip is a simple introduction to triggers and will only touch the subject enough to make you capable of defining your first simple trigger for data validation.

A good thing about triggers are in my opinion that they may be activated on existing table and thereby introduce new validation rules that will not apply to existing rows in the table. Consequently they are not as severe as RI and table constraints. And you can easily remove them again. It is also possible to control whether a trigger must be activated for insert, update or delete. This gives you the possibility to introduce different validations depending on the situation.

Here is an example of a simple socalled before trigger that does a litte validation of the data that may be inserted into a table. A before trigger is activated before the native sql action (in this case an insert) is carried out:

CREATE TRIGGER mycheck NO CASCADE BEFORE
INSERT ON mytable REFERENCING NEW AS mt
FOR EACH ROW MODE DB2SQL WHEN
( NOT EXISTS (SELECT 0 FROM mycodes WHERE mycode = mt.mycol1)
  AND NOT mt.mycol2 BETWEEN 1 AND 9
) SIGNAL SQLSTATE '75001' ('Columns mycol1 or mycol2 invalid')
;

This trigger will be created using the name MYCHECK. The creator will be the value of current SQLID and by the way the phrase creator does not apply, it is now called a schema name. Triggers may only have names 8 characters long (DB2 version 8 NFM allows up to 128 characters). NO CASCADE BEFORE indicates that it is a before trigger. INSERT indicates that the trigger is activated for INSERT SQL statements on MYTABLE. You can only use one operation for each trigger. You cannot define a common trigger for two different operations so you have to define two triggers even though they might carry out the same validation. REFERENCING NEW AS indicates that columns in the new row may be referenced using the indicated name as a correlation id. In a trigger for an update operation you may also use REFERENCING OLD AS and hence you may compare values from the existing row with the new row. FOR EACH ROW MODE DB2SQL is mandatory for a before trigger.

WHEN indicates in the parenthesis the validation that must be carried out. In the example a validation against another table is carried out (this is just like a RI validation) and another column is validates for specific values. If the validation is true the SQL statement after the parenthesis is carried out. You can only specify one WHEN in each trigger, but the number of SQL statements after WHEN is not limited in any way. If you want to carry out more than one SQL statement in the trigger you must specify BEGIN ATOMIC before the first SQL statement, terminate each SQL statement with a ; and write END after the last ;. When using ; inside SQL statements but outside constant values a lot of the tools used for executing dynamic SQL are challenged because they use ; as a SQL statement separator. Therefore CREATE TRIGGER may cause some problems. In the versions of SPUFI that match version 6 and following versions, you can select another SQL statement separator so you are able to define your triggers successfully. I will also advise you to write END with capital letters ( at least if you are still on version 6) as my first CREATE TRIGGER attempts failed until I wrote END with capital letters

SIGNAL SQLSTATE is a new SQL statement only available in triggers. When it is executed a SQLCODE of -438 is returned by the SQL statement executing the trigger. The field SQLERRMC in the SQLCA then contains the text specified in the parenthesis. SQLERRMC is a part of the field SQLERRM which also contains field SQLERRML that is a two byte integer field indicating the number of bytes in SQLERRMC that is filled with valid information. The field SQLSTATE in SQLCA will contain the indicated SQLSTATE value. You cannot use any value in SQLSTATE. Look in the SQL reference for more help on this subject.

I will advice you to try and use triggers in many different ways before you use them in a production environment. Your installation may have chosen not to use triggers or may not have proper migration procedures for triggers. You might run into any kind of other trouble before reaching your production environment. If you want to check whether any other triggers has been created on your DB2 subsystem, you must look in the SYSIBM.SYSTRIGGERS catalog table. In this table you can see the actual source text for the successfully defined CREATE TRIGGER SQL statements.