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 «««56789»»»

Automated Trigger To Require a WHERE Clause Expand / Collapse
Author
Message
Posted Wednesday, January 26, 2011 11:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 6, 2011 11:44 AM
Points: 25, Visits: 15
I think this is false advertising. You are really NOT stopping an update or delete statement without a "WHERE" clause from running, you are just comparing the number of rows affected to the number of rows in the table, or maybe not, as others have been added concerns about index's adding to the rowcount.
What if someone actually does need to update all the rows in the table. What if the update statement does have a "WHERE" clause and all of the table rows meet the criteria of the where clause.
I can see this creating problems, especially down the road when someone else is maintaing the database. I would not recommend using this code, sorry.
Post #1054103
Posted Wednesday, January 26, 2011 11:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 1, 2014 3:06 AM
Points: 6, Visits: 49
Thanks LH.
I thought of the Truncate table after I posted my doubt on the requirement to Delete all rows (with these triggers on).
Thanks for your confirmation.
Post #1054123
Posted Wednesday, January 26, 2011 3:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 233, Visits: 494
Rahul The Dba (1/25/2011)
aaron-403220 (1/25/2011)
Just to ask a foolish question, but is that real data?
Are those real names and birthdates?


What will you do with that ????????

Still i am answering your foolish question, yes it is a real data


You should be fired for your incredible incompetence and stupidity.


Random Technical Stuff
Post #1054264
Posted Wednesday, January 26, 2011 3:45 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:52 AM
Points: 368, Visits: 3,070
ta.bu.shi.da.yu (1/26/2011)
Rahul The Dba (1/25/2011)
aaron-403220 (1/25/2011)
Just to ask a foolish question, but is that real data?
Are those real names and birthdates?


What will you do with that ????????

Still i am answering your foolish question, yes it is a real data


You should be fired for your incredible incompetence and stupidity.


The above comment may sound harsh, however the poster is correct.

You have shown a flagrant disregard for data security. Is this data sourced from your employer? In the UK, we have Data Protection laws. If I was to have exposed 'real' data as you have, I would be fired and my employer fined.

As a DBA, you are the guardian of the data. You however have shown a very laxadaisical attitude to the trust placed in you.

Your comment above also indicates that you don't realise what you have done wrong. That makes it worse as you consider yourself a dba.
Post #1054281
Posted Wednesday, January 26, 2011 3:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 3:09 AM
Points: 71, Visits: 177
Perhaps this has been already said but in my opinion author makes VERY important FALSE assumption.

The condition @@ROWCOUNT < table/index/statistic COUNT has NOTHING in common with presence or not WHERE clause in SQL Query.

Prove: UPDATE Whatever WHERE 1=1

So, since fundamental assumption of solution is PLAIN WRONG, solution based on it can not be correct.

It is nice problem workaround, It might work, might work even in most cases, but IMHO correct solution is NOT to allow developer to run untested queries against production server.... Or - retrieve original query text from DMV and test for presence WHERE clause in query text...


Post #1054282
Posted Thursday, January 27, 2011 1:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:53 AM
Points: 2,529, Visits: 2,402
Avoid beginners to update production table is the better way to avoid disaster!
In the author concept thers's a BIG BUG!
If before the trigger as described in the article another trigger is called the @@rowcount may be modified and the check will fail.
Remember: @@ROWCOUNT is countinuosly modified from SELECT, UPDATE, INSERT, DELETE.
When you create the trigger as in the article, you should be sure that your trigger is called first.

SET @Count = @@ROWCOUNT; -- Are you sure that this trigger is called first? Another one may modify @@rowcount
IF @Count >= (SELECT SUM(row_count)
FROM sys.dm_db_partition_stats

WHERE OBJECT_ID = OBJECT_ID('table')

)

Here a simple example of the BUG.
create table tmptmp(a sysname primary key)
insert tmptmp select name from sys.objects
go
create trigger tr_tmptmp on tmptmp
for update
as
begin

select 1 -- modify @@rowcount
print @@rowcount
end
go
create trigger tr_tmptmp_1 on tmptmp
for update
as
begin
print @@rowcount
end
go

update tmptmp set a = a

Post #1054409
Posted Thursday, January 27, 2011 8:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 6, 2011 11:44 AM
Points: 25, Visits: 15
That is an excellant point, what if you were checking if there was actually a "WHERE" clause in the SQL statment it could be "WHERE 1 = 1". I have seen people use this code.
Post #1054614
Posted Thursday, January 27, 2011 11:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 25, 2013 8:58 AM
Points: 2, Visits: 94
Carlo Romagnano (1/25/2011)
luciano furlan (1/25/2011)
It is a smart workaround, but it doesn't really solve the problem in an elegant way.
I miss a configuration on Sql server that states "Where Clause mandatory". For all tables, for all Sql statements.
I fear every day, that I will forget the where clause eventually and destroy an entire table.
And guess what: Sql doesn't have an Undo button!

But still if you need (and you will need) you can write "Where 1=1", to update/delete every record in a table.
Your workaround treats it like a "missing where".
You can see the difference between an accidentaly forgoten where and one you written on purpose.

I believe this is a huge "security hole" in the Sql language. And yet it stays with us for so many years (and so many tears).



I disagree: what about using transactions?
In general, this trigger affects performance just to prevent programmers errors.

Before any DDL or MDL I use the fantastic "SET IMPLICIT_TRANSACTIONS ON".
and only when I sure of modification I run the COMMIT command.


I think the following DELETE trigger migth do the trick. I am not skilled with PATINDEX so that part can properly better, but the DELETE trigger will catch the current statement.
The script is for MS-SQL2008R2, but MS-SQL2005 should also work.
I do not recommend this method on a table with heavy (delete) traffic since the in-memory table is expensive.
Those word said, here is my suggestion (the DELETE trigger is in Bold, the other stuff is just for testing):

-- ====================
-- Create a test table
-- ====================
create table foo
(
field1 varchar(10)
);
GO




-- ===================================
-- Create DELETE trigger on table foo
-- ===================================
CREATE TRIGGER tr_foo_delete
ON foo
FOR DELETE
AS
DECLARE @T TABLE
(
language_event nvarchar(100),
parameters INT,
event_info nvarchar(4000)
,event_time datetime DEFAULT CURRENT_TIMESTAMP
);
DECLARE @pi int;
DECLARE @errmsg varchar(300);
INSERT INTO @T(
language_event
,parameters
,event_info
--,event_time
)
EXEC ('DBCC INPUTBUFFER(@@SPID);');

-- ==============================================
-- TEST for expression ...WHERE columnname = ...
-- ==============================================
SELECT @pi = PATINDEX('%WHERE%[a-z]%=%_', (SELECT TOP 1 event_info from @T))
IF @pi = 0
BEGIN
-- ==============================================
-- TEST for expression ...WHERE ? = columnname...
-- ==============================================
SELECT @pi = PATINDEX('%WHERE%=%[a-z]', (SELECT TOP 1 event_info from @T));
END

-- ================================================
-- If a (poor man) match was not found raise error
-- ================================================
IF @pi = 0
BEGIN
SELECT TOP 1 @errmsg = 'Delete without valid WHERE is prohibited: ' + Left(event_info, 200) from @T;
RAISERROR (@errmsg, 16, 1);
ROLLBACK TRANSACTION;
END
GO


-- ================================================
-- Test the DELETE trigger
-- ================================================
INSERT into foo (field1) values ('abcd');
INSERT into foo (field1) values ('1');
GO
DELETE FROM foo;
GO
SELECT 1, 'DELETE is catched', * FROM foo;
GO
DELETE FROM foo WHERE 1 = 1;
GO
SELECT 2, 'DELETE is catched', * FROM foo;
GO
DELETE FROM foo WHERE field1 = '1';
GO
SELECT 3, 'DELETE is valid', * FROM foo;
GO
DELETE FROM foo where field1 = 'abcd';
GO
SELECT 4, 'DELETE is valid', * FROM foo
UNION
SELECT 4, 'DELETE is valid', '';
GO
DROP TABLE foo;
GO




Kind regards LH
Post #1054795
Posted Thursday, January 27, 2011 12:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 10:18 AM
Points: 33, Visits: 74
Thank you rjohal-500813 and ta.bu.shi.da.yu.

As an university employee at a hospital in Canada, I too would be fired and the provincial privacy office would be asking serious questions about our project.
Post #1054854
Posted Friday, January 28, 2011 10:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 7:44 AM
Points: 256, Visits: 167
Hi Rahul,

I am just new to sql server. But I just want to know one simple question what will be the statement if i want to update a particular column of all rows i.e.
UPDATE tablename SET name = 'test'

Will it work?

Thanks,
Post #1055708
« Prev Topic | Next Topic »

Add to briefcase «««56789»»»

Permissions Expand / Collapse