SELECT Event (IDS)

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page SQL Statements > CREATE TRIGGER >

SELECT Event (IDS)

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:
 
|--SELECT--+----------------+--ON--table------------------------|
           |     .-,------. |
           |     V        | |
           '-OF----column-+-'

Element	Description	Restrictions	Syntax
column	Column that activates the trigger	Must exist in the triggering table	Identifier, p. Identifier
table	Name of the triggering table	Must exist in the database	Identifier, p. Identifier

If you define more than one Select trigger on the same table, the column list is required, and the column lists for each trigger must be mutually exclusive.

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('for each action'))

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