Disaster Prevention by Triggers

In my previous post, I discussed recovery from a situation where a user had updated every row of a table rather than the one that he was aiming at. At the end of that post, I suggested that a way around this would be to use a trigger on the table. I’m going to discuss that here.

Theory

A DML (Data Manipulation Language) trigger is similar to a stored procedure that fires automatically when (in this case) a table is updated (through an INSERT, UPDATE or DELETE statement). Behind the scenes, your trigger code can view the data that is being inserted or deleted (or updated – a combination of the two), and behave according to what it sees. In this particular example, we are looking at setting up a trigger that will cause a task to fail if more than one row is affected.

Practical

Examining the MSDN documentation for CREATE TRIGGER, we are looking for an AFTER trigger which will check the “magic tables” (bad naming, but read this article by Jen “MidnightDBA” McCown, and the name will stick in your mind too). If these back-end tables contain more than one row, then this means that more than one row is affected by the statement being executed, and so we can generate an appropriate failure, error message, and rollback.

So. We need a table to play with:

create table TriggerTest (ID integer not null identity (1, 1), Description varchar(50))
insert into TriggerTest (Description) values ('foo'), ('bar'), ('baz')

Yes, it’s unimaginatively named, and contains unimaginative data. But it’s enough to show the point.

Let’s now create the trigger:

CREATE TRIGGER NoBigUpdates ON TriggerTest AFTER UPDATE, DELETE
AS
  DECLARE @RowCount int
  SELECT @RowCount = COUNT(1) FROM deleted
  IF @RowCount > 1 
    BEGIN
      RAISERROR ('You are attempting to update or delete more than one row of data', 16, 1)
      ROLLBACK TRANSACTION
      RETURN
    END
GO

Things to note:

Because I’m looking for mass updates and deletions, that’s all I say in the AFTER clause. I’m still allowing bulk inserts of data.
Note the use of the deleted table. This contains all rows affected by the DELETE statement, or all UPDATEd rows in their original state. (The other table, whose purpose you can guess, is called inserted.)
Because we’re not complete bastards, we’re using the badly named RAISERROR sending back a friendly error message to client applications.
Because I’m generally not working with SQL Server 2012, I use RAISERROR rather than THROW. Note the differences between RAISERROR and THROW discussed in the documentation.
After raising the error, we’re rolling back the transaction, because we don’t want people to be able to change these rows. You might consider writing a record to another table indicating that this trigger had been called, so you can have a little word with the appropriate user…

In use

So, let’s have a play, and see what we can do.

UPDATE TriggerTest SET Description = 'fu' WHERE Description = 'foo'

That works. What about multiple rows?

UPDATE TriggerTest SET Description = 'barf'

And we get the following message, helpfully displayed in SSMS in bright red:

Msg 50000, Level 16, State 1, Procedure NoBigUpdates, Line 8
You are attempting to update or delete more than one row of data
Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.

You get similar results when running

DELETE FROM TriggerTest WHERE Description = 'fu'
DELETE FROM TriggerTest

The first line succeeds, the second fails with the same message.

Let’s try multiple INSERTs:

insert into TriggerTest (Description) SELECT Description FROM TriggerTest

Yup, that works just fine.

Not a cure-all

HOWEVER… This trigger will not prevent someone running:

TRUNCATE TABLE TriggerTest

as a TRUNCATE is not the same as a DELETE statement. You could always try to prevent people from doing that by not granting ALTER rights on the table…

Still. It’s a start.