10.3  Trigger

Similar to SQL stored procedures, database triggers are an implementation of 

SQL/PSM. A database trigger is a database object containing application logic, 

which is activated when a particular event or operation occurs on a database 

table. For example, you could set up a stored procedure that checks the credit 

rating for a customer, and is triggered on each insert of a new customer in the 

customer table. DB2 UDB provides a matured implementation of database 

triggers. However, since V5.1 of MySQL is not just around the corner, we limit 

this section to the very basics of database triggers.

Unlike stored procedures triggers are associated with a database 

table

, an 

operation

 (INSERT, UPDATE, DELETE) on the table and a 

point in time

(BEFOR, AFTER) which all have to apply for the trigger to be activated. Here are 

some examples where triggers might be useful:

When inserting rows in a table, triggers can be used to supply, validate or 

manipulate data before allowing an insert operation to occur.

When updating rows, triggers can be used to compare old and new values 

and allow proper state transition. For example, a date value can only change 

to a future never to a past value.

Auditing and additional logging can be implemented upon deletion of rows.

Example 10 5 shows a simple example which inserts the value of 

start + 45 

minutes

 into column 

end 

in table 

mytab

if there is no value for column 

end

supplied on the 

insert

 statement activating the trigger

Example 10 5   Simple TRIGGER 

CREATE TRIGGER FirstTrg 

NO CASCADE BEFORE INSERT ON mytable

REFERENCING NEW AS n

REFERENCING OLD AS o

FOR EACH ROW                        

MODE DB2SQL

WHEN (n.end IS NULL)

    SET n.end = n.start + 45 MINUTES

10.4  User defined data types (UDT)

A lot of application programming languages are based on object oriented 

analysis and design because of reasons like simplicity, scalability, and easier 

modeling of complex business objects and services. DB2 UDB supports a few 

object oriented programming features which, you can incorporate object oriented 

324 

MySQL to DB2 UDB Conversion Guide