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.

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 on a table. See also the sections Action Clause and Triggered-Action List.

A triggering statement from an external database server can activate the trigger.

As the following example shows, an Insert trigger on newtab, managed by dbserver1, is activated by an INSERT statement from dbserver2. The trigger executes as if the INSERT originated on dbserver1.

-- Trigger on stores_demo@dbserver1:newtab
CREATE TRIGGER ins_tr INSERT ON newtab
   REFERENCING new AS post_ins
   FOR EACH ROW(EXECUTE PROCEDURE nt_pct (post_ins.mc));
-- Triggering statement from dbserver2
INSERT INTO stores_demo@dbserver1:newtab
   SELECT item_num, order_num, quantity, stock_num, manu_code,
   total_price FROM items;

Dynamic Server also supports INSTEAD OF triggers on views, which are initiated when a triggering DML operation references the specified view. The INSTEAD OF trigger replaces the trigger event with the specified trigger action on a view, rather than execute the triggering INSERT, DELETE, or UPDATE operation. 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.

Trigger Events with Cursors

For triggers on tables, if the triggering statement uses a cursor, each part of the trigger action (including BEFORE, FOR EACH ROW, and AFTER, if these are specified for the trigger) is activated for each row that the cursor processes.

This behavior differs from what occurs when a triggering statement does not use a cursor and updates multiple rows. In this case, any BEFORE and AFTER triggered actions execute only once, but the FOR EACH ROW action list is executed for each row processed by the triggering statement. For additional information about trigger actions, see Action Clause

Privileges on the Trigger Event

You must have appropriate Insert, Delete, Update, or Select privilege on the triggering table or view to execute a triggering INSERT, DELETE, UPDATE, or SELECT statement as the trigger event. The triggering statement might still fail, however, if you do not also have the privileges necessary to execute one of the SQL statements in the trigger action. When the trigger actions are executed, the database server checks your privileges for each SQL statement in the trigger definition, as if the statement were being executed independently of the trigger. For information on the privileges needed to execute the trigger actions, see Privileges to Execute Trigger Actions.

Performance Impact of Triggers

The INSERT, DELETE, UPDATE, and SELECT statements that initiate triggers might appear to execute slowly because they activate additional SQL statements, and the user might not know that other actions are occurring.

The execution time for a trigger event depends on the complexity of the trigger action and whether it initiates other triggers. The time increases as the number of cascading triggers increases. For more information on triggers that initiate other triggers, see Cascading Triggers.