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
-- Create DELETE trigger on table foo
CREATE TRIGGER tr_foo_delete
DECLARE @T TABLE
,event_time datetime DEFAULT CURRENT_TIMESTAMP
DECLARE @pi int;
DECLARE @errmsg varchar(300);
INSERT INTO @T(
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
-- TEST for expression ...WHERE ? = columnname...
SELECT @pi = PATINDEX('%WHERE%=%[a-z]', (SELECT TOP 1 event_info from @T));
-- If a (poor man) match was not found raise error
IF @pi = 0
SELECT TOP 1 @errmsg = 'Delete without valid WHERE is prohibited: ' + Left(event_info, 200) from @T;
RAISERROR (@errmsg, 16, 1);
-- Test the DELETE trigger
INSERT into foo (field1) values ('abcd');
INSERT into foo (field1) values ('1');
DELETE FROM foo;
SELECT 1, 'DELETE is catched', * FROM foo;
DELETE FROM foo WHERE 1 = 1;
SELECT 2, 'DELETE is catched', * FROM foo;
DELETE FROM foo WHERE field1 = '1';
SELECT 3, 'DELETE is valid', * FROM foo;
DELETE FROM foo where field1 = 'abcd';
SELECT 4, 'DELETE is valid', * FROM foo
SELECT 4, 'DELETE is valid', '';
DROP TABLE foo;
Kind regards LH