SELECT Statements That Execute Triggered Actions

When you create a select trigger, only certain types of select statements can execute the actions defined on that trigger. A select trigger executes for the following types of SELECT statements only:

• Stand-alone SELECT statements
• Collection subqueries in the select list of a SELECT statement
• SELECT statements embedded in user-defined routines
• Views

Stand-Alone SELECT Statements

Suppose you define the following select trigger on a table:

CREATE TRIGGER hits_trig SELECT OF col_a ON tab_a
REFERENCING OLD AS hit
FOR EACH ROW (INSERT INTO hits_log
VALUES (hit.col_a, CURRENT, USER));

A select trigger executes when the triggering column appears in the select list of a stand-alone SELECT statement. The following statement executes a triggered action on the hits_trig trigger for each instance of a row that the database server returns:

SELECT col_a FROM tab_a

Collection Subqueries in the Select List of a SELECT Statement

A select trigger executes when the triggering column appears in a collection subquery that occurs in the select list of another SELECT statement. The following statement executes a triggered action on the hits_trig trigger for each instance of a row that the collection subquery returns:

SELECT MULTISET(SELECT col_a FROM tab_a) FROM ...

SELECT Statements Embedded in User-Defined Routines

A select trigger that is defined on a SELECT statement embedded in a user defined routine (UDR) executes a triggered action in the following instances only:

• The UDR appears in the select list of a SELECT statement
• The UDR is invoked with an execute procedure statement

Suppose you create a routine new_proc that contains the statement SELECT col_a FROM tab_a. Each of the following statements executes a triggered action on the hits_trig trigger for each instance of a row that the embedded SELECT statement returns:

SELECT new_proc() FROM tab_b
EXECUTE PROCEDURE new_proc

Views

Select triggers execute a triggered action for views whose base tables contain a reference to a triggering column. However, you cannot define a select trigger on a view.

Suppose you create the following view:

CREATE VIEW view_tab AS
SELECT * FROM tab_a

The following statements execute a triggered action on the hits_trig trigger for each instance of a row that the view returns:

SELECT * FROM view_tab

SELECT col_a FROM tab_a