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