Have you ever face the challenge of filtering the statements attempt to update a record based on computer name, login name or even application name.

Extending Trigger functionality in SQL 2005 to cover DDL statements was a big step towards more control on the structure and Auditing DDL changes. With this new type of trigger a new function called EventData() has been introduced to SQL users which will generate a XML string when it is call from within trigger, and the XML will present details such as login name, spid , SQL command, hostname, object name about the statement that ran and initiated the trigger to fire.

Unfortunately there are limitations on the DML side. Let start with a simple scenario: my aim in this example is to ignore any updates from computer called srv-dba01 and the update statement generated from other computers against this table can only update a certain column. (Two levels of filtering)

The first part of the script creates a database called Trigger-Test and a table within it called TableA and gets populated with three rows.

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
-- populate 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

The next part of the code will create an update Trigger on TableA

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;
 raiserror('srv-dba01 is not allowed to do updates on TableA',16,1);
end
else 
begin
 if update(name)
 begin 
 Commit transaction;
 raiserror('Name column of TableA has been updated successfully',16,1);
 end
 else 
 begin
 Rollback transaction;
 raiserror('Failed - Name column is the only column that can be updated',16,1);
 end
end
go

@@SPID within the trigger will reveal the SPID of the session which the is running the update statement. And with knowing the spid of the session and looking up sys.sysprocesses or sys.dm_exec_sessions table, other information of the session like Hostname, login name, application name can be revealed. In this example we will be using the host name (computer name) and with a simple if condition we can rollback the update initiated from that host name. Also with the update () function which is operational only within trigger we can check if the updated column in the one that updates are permitted against it. Running following update statement against the newly created table from srv-dba01 (machine) will produce the following error message

update tableA set name ='Jane' where id=8
(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure TR_filtering_updates_on_TableA, Line 11
srv-dba01 is not allowed to do updates on TableA
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Running following update statement against the newly created table from any other machine ( not srv-dba01 ) will produce the following message.

update tableA set id=123 where name='john'
(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure TR_filtering_updates_on_TableA, Line 23
Failed-Name column is the only column that can be updated
Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.

The message is the result of the update() function of the name column, and it will prevent the update on an id or any other column. The only column that is updatable is name in this example

update tableA set name ='Jane' where id=8
(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure TR_filtering_updates_on_TableA, Line 18
Name column of TableA has been updated successfully
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Conclusion

Using this technical the update, insert, and delete statements can be filtered by loginname, host name and application name. This allows you to exercise greater control over the actions allowed by your users.