January 26, 2011 at 11:40 am
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.
January 26, 2011 at 11:57 am
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.
January 26, 2011 at 3:15 pm
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.
January 26, 2011 at 3:45 pm
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.
January 26, 2011 at 3:46 pm
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...
January 27, 2011 at 1:28 am
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
January 27, 2011 at 8:24 am
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. 🙂
January 27, 2011 at 11:27 am
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
January 27, 2011 at 12:40 pm
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.
January 28, 2011 at 10:29 pm
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,
January 29, 2011 at 4:17 am
Subhash Kr Singh (1/28/2011)
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?
Sorry but you seems to be new to English as well..
Article title says: Automated Trigger To Require a WHERE Clause
Troubles with reading or understanding ?
January 29, 2011 at 12:24 pm
Subhash Kr Singh (1/28/2011)
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,
Hi Subhash
Best way to answer questions like this is to have a go! SQL Server Express is free, and the syntax is the same as all the paid-for versions. Set up a test environment and try some things out.
The straight answer to your question is yes, what you have written will update *all* the rows. That leads to the point of this article - if in standard SQL you issue an update or delete command without specifying which rows you want it to apply to (i.e. without a WHERE clause), it will indeed update all the rows. It is an easy mistake to make - to forget the WHERE clause and then end up trashing all the rows - which is why the author of the article we're discussing came up with a (flawed) trigger to try to prevent that.
Jinlye
PS Don't be put off asking because of earlier responses.
January 29, 2011 at 12:46 pm
jinlye (1/29/2011)
Subhash Kr Singh (1/28/2011)
UPDATE tablename SET name = 'test'Will it work?
...The straight answer to your question is yes...
PS Don't be put off asking because of earlier responses.
Dear Jinlye, you are wrong, I'm afraid.
In context of this article (and applied trigger) the query will NOT work.
In context of answering general questions about such a basic SQL queries like this - this is wrong thread and/or wrong forum to ask such a question and also to answer it.
That's my humble opinion.
January 29, 2011 at 1:31 pm
Slawek Guzek (1/29/2011)
Dear Jinlye, you are wrong, I'm afraid...That's my humble opinion.
There is nothing humble about your opinion.
If you read his post, you will see he is correct and is actually taking the time to explain his answer (you seemed to have edited out the explanation in context to the article and replaced it with an ellipsis.) You, however, do not offer any coherent explanation and simply ask the poster if he is stupid in about as many words.
If you would prefer to take portions of the post out of context to 'prove' you're right, have at it, but you are simply embarrassing yourself on this forum.
SQL guy and Houston Magician
January 29, 2011 at 1:40 pm
In context of this article (and applied trigger) the query will NOT work.
Ah. Good point. Subhash's question could have been meant in one of two ways: 1) Will this query work generally (yes, affecting all rows), or 2) Will this query work if I have Rahul's trigger installed (no, it will give the error message raised by the trigger and roll back).
In context of answering general questions about such a basic SQL queries like this - this is wrong thread and/or wrong forum to ask such a question
Yes, I guess it is. And nicely put, too.
...and also to answer it.
Ah well - that's nice for Subhash - between us we've answered it for him, whichever way he meant it.
Jinlye
Viewing 15 posts - 61 through 75 (of 94 total)
You must be logged in to reply to this topic. Login to reply