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