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.
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
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.
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
AFTERclause. I’m still allowing bulk inserts of data.
- Note the use of the
deletedtable. This contains all rows affected by the
DELETEstatement, or all
UPDATEd rows in their original state. (The other table, whose purpose you can guess, is called
- Because we’re not complete bastards, we’re using the badly named
RAISERRORsending 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…
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.