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

BEGIN CALLOUT A

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

END CALLOUT A

BEGIN CALLOUT B

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

END CALLOUT B