SELECT Event

DELETE and INSERT events are defined by those keywords (and the ON table clause), but SELECT and UPDATE events also support an optional column list.

SELECT Event


Element	Description	Restrictions	Syntax
column	Column that activates the trigger	Must exist in the triggering table	Identifier
owner	Owner of table	Must own table	Owner name
table	Name of the triggering table	Must exist in the database	Identifier

If you define more than one Select trigger on the same table, the column list is optional, and the column lists for each trigger can be unique or can duplicate that of another Select trigger.

A SELECT on the triggering table can activate the trigger in two cases:

The SELECT statement references any column in the column list.
The SELECT event definition has no OF column list specification.

(Sections that follow, however, describe additional circumstances that can affect whether or not a SELECT statement activates a Select trigger.)

Whether it specifies one column or more than one column from the column list, a triggering SELECT statement activates the Select trigger only once.

The action of a Select trigger cannot include an UPDATE, INSERT, or DELETE on the triggering table. The action of a Select trigger can include UPDATE, INSERT, and DELETE actions on tables other than the triggering table. The following example defines a Select trigger on one column of a table:

CREATE TRIGGER mytrig 
   SELECT OF cola ON mytab REFERENCING OLD AS pre
   FOR EACH ROW (INSERT INTO newtab VALUES('for each action'));

You cannot specify a SELECT event for an INSTEAD OF trigger on a view.