Select triggers

When the CREATE TRIGGER statement defines as its triggering event any query on a specific table ( 

SELECT ON table
or 
SELECT ON column-list ON table
), the resulting trigger object is a Select trigger on the specified table. The same trigger can also be activated by queries on a view that includes triggering columns from table as its base table. SELECT statements cannot, however, be the trigger events for INSTEAD OF triggers on a view.

If the CREATE TRIGGER statement also includes a column-list in the definition of an enabled Select trigger event, and the Projection list of a subsequent query on the specified table does not include any of the specified columns, that query cannot be a triggering event for the Select trigger.

Warning:

Select triggers are not reliable for auditing. Do not attempt to create a Select trigger on a table, or on a subset of its columns, for the purpose of performing application-specific auditing. In general, it is not possible, to track the number of SELECT actions on a table by creating a Select trigger to insert an audit record into an audit table each time a user queries a certain table.

For example, suppose that you define a Select trigger on the table AuditedTable and that a user who holds Select privileges on AuditedTable issues the following query:

SELECT a.* FROM (SELECT * FROM AuditedTable) AS a;

The database server issues no error, but the SELECT trigger on AuditedTable will not be activated by this query. A query that included a set operator, such as UNION or INTERSECT, or any other syntax that Select triggers do not support, would be similarly invisible to an audit-record strategy that is based on Select triggers.

Because of the numerous restrictions on the execution of Select triggers, as partially listed in this chapter, the resulting Select trigger actions will typically correspond to only a subset (that might be empty) of whatever logical Select events you are attempting to enumerate.