Select Trigger

As we all know, in SQL Server 2000 we can define triggers only for Insert, Update or Delete commands. Sometimes we would like to have a trigger that would be fired on Select statement. This of course can’t be achieved directly; still, there is a workaround.

SQL Profiler can save trace to a trace file or to a table. And if the trace is saved in a table, Profiler actually inserts rows into that table. Voila! In the trace definition be sure to filter out all events except selects on the table(s) of interest, and save the trace in a table. Then create an Insert trigger on the trace table, and this trigger will fire on selects that use table(s) of interest.

It seems easy, but we have a small problem. Trace table is created by Profiler client part; you can’t create it programmatically using the sp_trace_create system stored procedure. Even more, the table is recreated every time you restart the trace (you have actually to repeat the process of defining the trace destination table). So you have to recreate the trigger every time you restart the trace. Also you have to use Profiler client tool on-line. Another problem is the TextData Profiler data column. You can’t use it inside the trigger, because the data type of this column is ntext. Still, you can use it in the trace definition, as long as your trigger does not use it (from the Inserted table, for example).

If you can check the Select asynchronously, then it is possible to do everything programmatically by saving the trace in a trace file and then using the fn_trace_gettable system function to read the saved data.

For example, let’s say we want to follow selects on the Customers table of the Northwind database. Create a trace with only the following settings:

–          SP:StmtCompleted and SQL: StmtCompleted events

–          EventClass, TextData, ApplicationName and SPID columns

–          DatabaseID Equals 6 (DB_ID() of the Northwind database) and TextData Like select%customers% filters

–          Name the trace SelectTrigger and save it to a table with the same name in the Northwind database.

Start the trace, and create the following trigger using Query Analyzer:

CREATE TRIGGER TraceSelectTrigger ON SelectTrigger

FOR INSERT

AS

EXEC master.dbo.xp_logevent 60000, ‘Select from Customers happened!’, warning

Now check how trigger works by performing couple of selects:

SELECT TOP 1 *

  FROM Customers

SELECT TOP 1 *

  FROM Orders

SELECT TOP 1 c.CustomerID

  FROM Customers c INNER JOIN Orders o

        ON c.CustomerID=o.CustomerID

With Event Viewer, check whether you got two warnings in the Application log for the 1^st and the 3^rd queries (the 2^nd should be filtered out).