Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Filtering DML Statements

By Shaunt Khalatian,

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.

Total article views: 4748 | Views in the last 30 days: 3
 
Related Articles
FORUM

After Update trigger does not catch all updated columns

The after update trigger recognizes only the last column updated

FORUM

Update Trigger Loop?

Finding updates to columns without multiple triggers

FORUM

when one column is updated, want to update another column in same table using trigger.

when one column is updated, want to update another column in same table using trigger.

FORUM

Insert data from TableA to TableB

Insert and Update from TableA to TableB

SCRIPT

Find List of Columns Updated inside Trigger

This will list the columns used by the update statement inside a trigger. More generic and useful fo...

Tags
auditing    
triggers    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones