![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Triggers
Triggers are snippets of code associated with a table that execute when
an INSERT,
UPDATE or DELETE
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
INSERT, UPDATE,
DELETE activity, called INSTEAD OF
triggers.
Triggers can be defined to perform the actions
BEFORE or AFTER
each ROW or each
STATEMENT.
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;
