SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automated Trigger To Require a WHERE Clause


Automated Trigger To Require a WHERE Clause

Author
Message
dd-1011661
dd-1011661
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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.
mpb-543430
mpb-543430
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 77
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.
ta.bu.shi.da.yu
ta.bu.shi.da.yu
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 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
Dwayne Dibley
Dwayne Dibley
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 3171
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.
Slawek Guzek
Slawek Guzek
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 245
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...
Carlo Romagnano
Carlo Romagnano
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4838 Visits: 3325
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



I run on tuttopodismo
dd-1011661
dd-1011661
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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. :-)
lars.hesselberg
lars.hesselberg
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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
aaron-403220
aaron-403220
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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.
Subhash Kr Singh
Subhash Kr Singh
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 210
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,
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search