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


  1. use master
  2. go
  3. if exists ( select 1 from sys.databases where name ='Trigger-Test')
  4. begin
  5. exec ('alter database \\[Trigger-Test\\] set single_user with rollback immediate')
  6. drop database \\[Trigger-Test\\]
  7. end
  8. go
  9. create database \\[Trigger-Test\\]
  10. go
  11. use \\[Trigger-Test\\]
  12. GO
  13. -- Create a test table
  14. create table TableA
  15. (
  16. Id int,
  17. Name varchar(20),
  18. Contactnumber varchar(20)
  19. )
  20. go
  21. -- popluate the test table with data
  22. insert TableA
  23. select 3,'John','0207 124 2123' union
  24. select 5,'Mike','0207 124 1487' union
  25. select 8,'Kelly','0207 124 3524'
  26. go



  1. if exists (select 1 from sys.triggers where name ='TR_filtering_updates_on_TableA')
  2. begin
  3. drop trigger TR_filtering_updates_on_TableA
  4. end
  5. go
  6. create trigger TR_filtering_updates_on_TableA
  7. on TableA
  8. for update
  9. as
  10. declare @HostName nvarchar(128)
  11. select @HostName=hostname from sys.sysprocesses where spid=@@spid
  12. if (ltrim(rtrim(@HostName))='srv-dba01')
  13. begin
  14. Rollback transaction;
  15. print 'srv-dba01 is not allowed to do updates on TableA'
  16. end
  17. else
  18. begin
  19. if update(name)
  20. begin
  21. print 'Name column of TableA has been updated successfully' end
  22. else
  23. begin
  24. Rollback transaction;
  25. print 'Failed - Name column is the only column that can be updated'
  26. end
  27. end
  28. go