Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filtering DML Statements Expand / Collapse
Author
Message
Posted Monday, February 9, 2009 12:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:54 AM
Points: 2, Visits: 165
Comments posted to this topic are about the item Filtering DML Statements
Post #652570
Posted Monday, February 9, 2009 7:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 1, 2014 8:25 AM
Points: 5, Visits: 95
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
Posted Monday, February 9, 2009 8:51 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
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
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 »

Add to briefcase

Permissions Expand / Collapse