Prevent Accidental Data Deletion with Two Simple Strategies

Article

It's a common challenge in large systems, and many of us have faced the consequences of deleting more data than intended. To prevent these types of incidents, here are two effective approaches to safeguard your data:

1. SQL Trigger to Prevent DELETE Without WHERE Clause

CREATE TRIGGER trg_PreventDeleteWithoutWheree
on TableName
INSTEAD OF DELETE
AS
BEGIN
   -- Check if any rows were attempted to be deleted (meaning no WHERE clause)
   IF @@ROWCOUNT >
   BEGIN
        -- Raise an error
        RAISERROR('DELETE statements must have a WHERE clause to prevent accidental full table deletion.'
, 16, 1)
        -- Optionally, rollback the transaction if you want to completely prevent the delete
        ROLLBACK TRANSACTION
   END
   ELSE
   BEGIN
   -- If no rows were attempted to be deleted.
   DELETE FROM TableName
   WHERE EXISTS (SELECT 1 FROM deleted WHERE TableName. Id = deleted. Id) ;
   END
END;

First approach is to use a SQL Trigger to ensure that no delete operation runs without a WHERE clause. A trigger can be created to fire on DELETE operations and validate the statement before it executes.

Prevents accidental deletes at the database level.
Provides an additional layer of protection, especially against unintentional bulk deletions.
Ensures that your data is protected even if an application bypasses safeguards.

Cons. Requires additional database configuration and can add complexity to your schema. Doesn’t offer as much flexibility for more complex delete scenarios.

2. Interceptor to Prevent DELETE Without WHERE Clause

public override InterceptionResult<int> NonQueryExecuting(
  DbCommand command,
  CommandEventData eventData,
  InterceptionResult<int> result)
{
  ReadOnlySpan<char> sqlSpan = command.CommandText. AsSpan().TrimStart();
   if (sqlSpan.StartsWith ("DELETE", StringComparison.OrdinalIgnoreCase) &&
       (sqlSpan. Indexof ("WHERE", StringComparison.OrdinalIgnoreCase) = -1))
   {
       throw new InvalidOperationException ("DELETE statements must include a WHERE clause.");
   }
    return base.NonQueryExecuting(command, eventData, result);
}

You can create a custom command interceptor to intercept any DELETE operation and ensure that it includes a WHERE clause. This prevents broad deletion commands from executing, ensuring that no rows are accidentally deleted without proper conditions.

Prevents accidental deletes via the application.
Helps enforce better practices for safe deletion operations.
Increases application-level security

Cons

While the performance overhead is generally acceptable, it can be configured to limit the scope, applying the interceptor only to specific queries or operations, thereby reducing unnecessary overhead.

Note. The same approach for preventing DELETE without a WHERE clause can be easily achieved with different ORMs.