Filtering DML Statements

SQL Server 2005 introduced the EVENTDATA function. When called from within a Data Definition Language (DDL) trigger, this function generates an XML string that provides details (e.g., login name, host name, object name) about the statement that caused the trigger to fire. However, you can't use the EVENTDATA function within a Data Manipulation Language (DML) trigger. This limitation makes it difficult to filter UPDATE and other DML statements based on the details of the triggering statement, such as the login or host name. Because of this limitation, I created a workaround.

The easiest way to understand the workaround is with a simple example, which the script in Listing 1 demonstrates. (You can download Trigger-Test.sql by clicking the Download the Code Here link near the top of the page.) This script provides two levels of filtering:

Let's look at the script in more detail. Callout A in Listing 1 creates a sample database called Trigger-Test, creates a table within it called TableA, and populates the table with three rows. The code in callout B creates an UPDATE trigger on TableA. Using @@SPID within the trigger will reveal the server process ID (SPID) of the session that’s running the UPDATE statement. Once you know the session's SPID, you can use it to look up other details (e.g., login name, host name) in the sys.sysprocesses or sys.dm_exec_sessions table. You can then use this information, for example, with a simple IF condition to rollback the update initiated from a specific host name such as srv-dba01. Also, you can use the UPDATE() function, which is operational only within a trigger, to verify if the updated column is the one in which updates are permitted.

Now that you know how the script works, let's look at some sample results. If you run the following UPDATE statement from srv-dba01 against the newly created table, it will produce the error message shown in Figure 1:

update tableA set name ='Jane' where id=8

Running the UPDATE statement

update tableA set id=123 where name='john'

against the newly created table from any other machine (i.e., not srv-dba01) will produce the error message shown in Figure 2.

The error message is the result of the UPDATE() function of the name column, and it will prevent an ID or any other column from being updated. The only column that’s updatable in the UPDATE statement

update tableA set name ='Jane' where id=8

is Name, as Figure 3 shows.

Using this technique, UPDATE, INSERT, and DELETE statements can be filtered by login name, host name, and more. This functionality ultimately gives you more control over queries.

Listing 1: Trigger-Test.sql

BEGIN CALLOUT A

use mastergoif exists ( select 1 from sys.databases where name ='Trigger-Test')begin exec ('alter database \[Trigger-Test\] set single_user with rollback immediate')drop database \[Trigger-Test\] end gocreate database \[Trigger-Test\] gouse \[Trigger-Test\] GO -- Create a test tablecreate table TableA(Id int,Name varchar(20),Contactnumber varchar(20))go-- popluate the test table with datainsert TableA select 3,'John','0207 124 2123' union select 5,'Mike','0207 124 1487' union select 8,'Kelly','0207 124 3524'  go

END CALLOUT A

BEGIN CALLOUT B

if exists (select 1 from sys.triggers where name ='TR_filtering_updates_on_TableA')begindrop trigger TR_filtering_updates_on_TableAend gocreate trigger TR_filtering_updates_on_TableAon TableAfor update asdeclare @HostName nvarchar(128)select @HostName=hostname from sys.sysprocesses where spid=@@spidif (ltrim(rtrim(@HostName))='srv-dba01')beginRollback transaction;print 'srv-dba01 is not allowed to do updates on TableA'endelse beginif update(name)begin print 'Name column of TableA has been updated successfully'endelse beginRollback transaction;print 'Failed - Name column is the only column that can be updated'end endgo

END CALLOUT B