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 a SELECT operation that queries the table. Each CREATE TRIGGER statement 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 triggers that are activated by INSERT, DELETE, UPDATE, or SELECT statements. For each view, you can define INSTEAD OF triggers that are activated by INSERT, DELETE, or UPDATE statements. Multiple triggers on the same table or view can be activated by different types of trigger events or by the same type of trigger event.

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 on a Table.

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;

HCL OneDB™ 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 any number of INSTEAD OF trigger defined for each type of INSERT, DELETE, or UPDATE triggering event.