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:

  • The script ignores UPDATE statements from a specific computer (srv-dba01).
  • UPDATE statements run from other computers against the specified table (TableA) can only update a specific column (Name).

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                              

Figure 1: Error messages showing srv-dba01 can't update TableA

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.

Figure 2: Error message showing that only the name column can be updated

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.

Figure 3: Message showing that TableA has been successfully updated

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


                              use master                              go                              if 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                               go                              create database \\[Trigger-Test\\]                               go                              use \\[Trigger-Test\\]                               GO                               -- Create a test table                              create table TableA                              (	                              	Id int,                              	Name varchar(20),                              	Contactnumber varchar(20)                              )                              go                              -- popluate the test table with data                              insert TableA                               select 3,'John','0207 124 2123' union                               select 5,'Mike','0207 124 1487' union                               select 8,'Kelly','0207 124 3524'                                go                              



                              if exists (select 1 from sys.triggers where name ='TR_filtering_updates_on_TableA')                              begin                              		drop trigger TR_filtering_updates_on_TableA                              end                               go                              create trigger TR_filtering_updates_on_TableA                              on TableA                              for update                               as                              declare @HostName nvarchar(128)                              select @HostName=hostname from sys.sysprocesses where spid=@@spid                              if (ltrim(rtrim(@HostName))='srv-dba01')                              begin                              		Rollback transaction;                              		print 'srv-dba01 is not allowed to do updates on TableA'                              end                              else                               begin                              		if update(name)                              		begin                               				print 'Name column of TableA has been updated successfully'		end                              		else                               		begin                              				Rollback transaction;                              				print 'Failed - Name column is the only column that can be updated'                              		end                               end                              go