Triggers are snippets of code associated with a table that execute when
action is performed upon that table.
There are also triggers which can be defined upon a view to allow
appropriate alternative actions when the view is used for
DELETE activity, called INSTEAD OF
Triggers can be defined to perform the actions
ROW or each
Several triggers can be defined for the same action on a table. Only one INSTEAD OF can be defined for each action on a view. When several triggers are defined for the same action on a table, they are executed in the same order that they were defined.
Actions that can be taken by a trigger include taking different actions based upon the values involved, affecting the contents of other tables, invoking stored procedures, or returning user-defined error conditions.
For example, given the prior AUTHORS table, with integer columns NONFICTIONBOOKS and FICTIONBOOKS that are summaries of the counts by BOOKTYPE for that author in BOOKS, one could define one of the triggers needed on the BOOKS table to maintain the summaries in the AUTHORS table, as shown in Listing 41.
Listing 41. Creating triggers on the BOOKS table
CREATE TRIGGER BOOKSIA AFTER INSERT ON BOOKS REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL WHEN (NEW.BOOKTYPE ='N') UPDATE AUTHORS SET NONFICTIONBOOKS=NONFICTIONBOOKS+1 WHERE AUTHORID=NEW.AUTHORID;
To remove the trigger, issue:
DROP TRIGGER BOOKSIA;