Triggers and SPL Routines
You cannot define a trigger in an SPL routine that is called inside a DML (data
manipulation language) statement. Thus, the following statement returns an
error if the sp_items procedure includes the CREATE TRIGGER statement:
INSERT INTO items EXECUTE PROCEDURE sp_items
(The DML statements are listed in “Data Manipulation Statements” on
page 1-10.) SPL variables are not valid in CREATE TRIGGER statements. An
SPL routine called by a trigger cannot perform INSERT, DELETE, or UPDATE
operations on any table or view that is not local to the current database.
See also “Rules for SPL Routines” on page 2-298 for additional restrictions on
SPL routines that are invoked in triggered actions.
Trigger Events
The trigger event specifies what DML statements can initiate the trigger. The
event can be an INSERT, DELETE, or UPDATE operation on the table or view, or
(for IDS tables only) a SELECT operation that manipulates the table. You must
specify exactly one trigger event. Any SQL statement that is an instance of the
trigger event is called a triggering statement.
For each table, you can define only one trigger that is activated by an INSERT
statement and only one trigger that is activated by a DELETE statement. The
same table, however, can have multiple triggers that are activated by UPDATE
or SELECT statements, provided that each trigger specifies a disjunct set of
columns in defining the UPDATE or SELECT event on the table.
The INSTEAD OF trigger replaces the trigger event with a triggered action.
A view can have no more than one INSTEAD OF trigger defined for each type
of event (INSERT, DELETE, or UPDATE). You can, however, define a trigger on
one or more other views, each with its own INSTEAD OF trigger. ♦
You cannot specify a DELETE event if the triggering table has a referential
constraint that specifies ON DELETE CASCADE.
You are responsible for guaranteeing that the triggering statement returns the
same result with and without the trigger action. See also the sections “Action
Clause” on page 2-281 and “Triggered-Action List” on page 2-288.
A triggering statement from an external database server can activate the
trigger.