Is it possible to prevent UPDATE or DELETE statements being executed without a WHERE clause?

Question

As the title suggests, we'd like to prevent - at least via manual interface - UPDATE and DELETE statements from being executed on a particular SQL Server (2008 or above) DB or table if they do not have a WHERE clause.

Is that possible?

Answer 1

You can. It's not ideal, but you can create table triggers to assess each UPDATE and DELETE to determine how many rows they'll affect.

Erik Darling blogged about it over on Brent Ozar's blog. Neither he nor I would necessarily recommend it, but if the situation is desperate enough, well, this would work.

Here's the relevant code from the link, in case, you know, Brent closes up shop. He's flighty.

CREATE OR ALTER TRIGGER dbo.UseAWhereClauseNextTime
ON dbo.TriggerTest
FOR UPDATE, DELETE
AS
    BEGIN

        DECLARE @rc INT = @@ROWCOUNT;
        DECLARE @table_rows INT = (   SELECT row_count
                                      FROM   sys.dm_db_partition_stats
                                      WHERE  object_id = OBJECT_ID('dbo.TriggerTest')
                                             AND index_id IN ( 0, 1 ));

        IF EXISTS ( SELECT 1 FROM Inserted ) AND EXISTS ( SELECT 1 FROM Deleted )
            BEGIN
                IF (( @rc * 100 ) / ( @table_rows )) >= 98
                    BEGIN
                        RAISERROR('USE A WHERE CLAUSE DUMMY', 0, 1) WITH NOWAIT;
                        ROLLBACK;
                    END;
            END;

        IF NOT EXISTS ( SELECT 1 FROM Inserted ) AND EXISTS ( SELECT 1 FROM Deleted )
            IF (   ( @table_rows = 0 )
                   OR (   @table_rows > 0
                          AND (( @rc * 100 ) / ( @table_rows + @rc )) >= 98 ))
                BEGIN
                    RAISERROR('USE A WHERE CLAUSE DUMMY', 0, 1) WITH NOWAIT;
                    ROLLBACK;
                END;

    END;

GO

The two clauses with (( @rc * 100 ) / ( @table_rows )) >= 98 set the threshold for a "bad" statement at 98% of the table affected, which Erik notes is sort of arbitrary, but also configurable.

Answer 2

If you are talking about Microsoft SQL Server Management studio then you have an option to use a plugin called SSMSBoost.

With it, you will get always a warning you when you are running DELETE without WHERE. Now I'm not 100% sure if this can be configured for UPDATE, but probably yes.

Answer 3

I think you're asking the wrong question. You want to prevent a delete that removes all the rows (or some high % of the rows), not prevent a delete that doesn't have a WHERE clause.

Whether nefarious or sloppy, intentional or not, I can easily defeat any parsing for an explicit WHERE clause, and still delete all the rows.

DELETE dbo.table WHERE 1 = 1;

DELETE dbo.table WHERE key_column > 0;

And intentional or not, I can dismiss, not read, or accidentally hit the Enter key or perform an errant mouse click for any popup a plug-in offers, so just because a plug-in gives me a warning, doesn't mean it will be able to prevent me from doing the thing I tried to do anyway.

And of course you can incorrectly block a query that affects as little as a single row if you think lack of a WHERE clause means it will delete all the rows. The following statements all should restrict to one affected row (or more with ties) even though there is no WHERE clause to be found:

DELETE TOP (1) dbo.table;

;WITH RowToDelete AS
(
  SELECT key_column FROM dbo.table
    GROUP BY key_column
    HAVING (key_column = 1)
)
DELETE RowToDelete;

;WITH RowToDelete AS
(
  SELECT key_column, rn = ROW_NUMBER() OVER (ORDER BY key_column)
    FROM dbo.table
)
DELETE TOP (1) RowToDelete;

If you want to actually prevent an accidental delete of all rows, you can do this in a trigger as suggested in another answer, just remember the trigger is there when you ever want to bypass it, and note that it doesn't help truncate or drop or transfer.