Using an INSTEAD OF DELETE Trigger to Prevent Row Deletions

I learned a nice trick today that works in versions of Microsoft SQL Server starting with SQL Server 2000, which introduces the INSTEAD OF triggers. In short, an INSTEAD OF trigger fires in place of the actual event, be it INSERT, UPDATE, or DELETE. That is pretty cool.

One of my clients had a table that has been mysteriously losing rows. This is a table that does not have any official applications written to delete rows from it, and rows should never be deleted from it. No one can figure out how the missing rows are being deleted. So I added an INSTEAD OF DELETE trigger that raises an error if someone tries to delete a row. It seems to work great.

As an example, I created one for the UnitMeasure table in the AdventureWorks sample database:

CREATE TRIGGER test_unit_measure_del_prevent
ON Production.UnitMeasure INSTEAD OF DELETE
AS
BEGIN
    RAISERROR ('Deletions not allowed from this table (source = instead of)', 16, 1)	
END

GO

I found this nice MSDN Magazine article about INSTEAD OF triggers [1] helpful and interesting.

Dan