Filtering DML Statements

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Shaunt Khaldtiance » Filtering DML Statements

Filtering DML Statements

Rate Topic

Display Mode

Topic Options

Author

Message

Shaunt_Kolf

Posted Monday, February 09, 2009 12:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 10, 2012 4:14 AM
Points: 2, Visits: 146

Comments posted to this topic are about the item Filtering DML Statements

Post #652570

Dan Mabee

Posted Monday, February 09, 2009 7:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 6:37 AM
Points: 5, Visits: 76

the column filtering solution is incomplete. consider this update statement:

update tableA set name ='Jane',contactnumber='baddata' where id=8

since column(name) comes up true, it still executes. column() doesn't mean only that column, just that it has to be included.

here's some logic i've used to make sure that the number of columns matches what i want:

declare @mc int
set @mc=0
declare @cu int
set @cu=columns_updated()
while @cu>0
begin
if (@cu & 1)>0
begin
set @mc=@mc+1
set @cu=@cu-1
end
set @cu=@cu/2
end
if @mc=1 and update(name)

Post #652780

noeld

Posted Monday, February 09, 2009 8:51 AM

SSCertifiable

Group: General Forum Members
Last Login: Thursday, June 06, 2013 9:12 AM
Points: 6,260, Visits: 1,980

Although the article shows a simple technique there are many things that were omitted.

This is NOT a way to handle such things if there are strict requirements from security standpoint.
1. You could potentially read the record, delete it and insert it back again with the changes you want.
The update trigger will never Know Sad

2. Workstation_id and application_name are values "settable" on the connection string and can be manipulated.
3. To Rollback in a trigger is a very expensive proposition for a busy system.
4. This kind of control should be performed using other security mechanisms.

All in all the article shows "a way" to do it I just wanted to point out that this is by no means THE way of doing such things if strict requirements are needed.

* Noel

Post #652885

« Prev Topic | Next Topic »

Permissions