Circumstances When a Select Trigger Is Not Activated

A SELECT statement on the triggering table does not activate a Select trigger in certain circumstances:

• If a subquery or UDR that contains the triggering SELECT statement appears in any clause of a SELECT statement other than the Projection clause or the FROM clause, the Select trigger is not activated.

  For example, if the subquery or UDR appears in the WHERE clause or HAVING clause of a SELECT statement, the SELECT statement within the subquery or UDR does not activate the Select trigger.

• If the trigger action of a Select trigger calls a UDR that includes a triggering SELECT statement, the Select trigger on the SELECT in the UDR is not activated. Cascading Select triggers are not supported.
• If a SELECT statement contains a built-in aggregate or user-defined aggregate in its Projection clause, the Select trigger is not activated. For example, the following SELECT statement does not activate a Select trigger defined on col1 of tab1:

  SELECT MIN(col1) FROM tab1;

• A SELECT statement that includes a set operator (including INTERSECT, MINUS, EXCEPT, UNION, or UNION ALL) does not activate a Select trigger.
• The SELECT clause of INSERT does not activate a Select trigger.
• A subquery in the WHERE clause of the DELETE or UPDATE statement cannot activate a Select trigger on the same table that the DELETE or UPDATE statement is modifying.
• If the Projection clause of a SELECT includes the DISTINCT or UNIQUE keywords, the SELECT statement does not activate a Select trigger.
• Select triggers are not supported on scroll cursors.
• If a SELECT statement refers to a remote triggering table, the Select trigger is not activated on the remote database server.
• Columns in the ORDER BY list of a query activate no Select triggers (nor any other triggers) unless they are also listed in the Projection clause.

An exception to the last restriction is that a Select trigger can be activated by a column in the ORDER BY list of a subquery in the FROM clause, whether or not the same column also appears in the Projection clause. In the following example, a table expression that includes col1 in the ORDER BY clause (but not in the select list of the Projection clause) is the triggering event for any enabled Select triggers that are defined on col1 of tab1:

SELECT vcol FROM (SELECT col2 FROM tab1 ORDER BY col1 ) vtab(vcol);