In nearly every project that I worked on in recent years, the database not only stores the data maintained by the application, but also describes (parts of) the application itself. If you ever had to implement a permission system which grants users to view or edit tables or open forms or execute functions, you already know that.
The resulting problem is that if the application relies on certain key data to be present and correct, accidental modification or deletion of that data usually causes the application to fail.
I try to show how to use triggers to prevent accidental data modification.
Prevent table data deletion
The simplest way to prevent data deletion is to have an INSTEAD OF DELETE trigger which does nothing, or simply raises an error:
CREATE TRIGGER [dbo].[Prevent_Foo_Delete]
ON [dbo].[Foo]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
RAISERROR('You cannot delete Foo',1,0);
END
If you really need to delete data, use the DISABLE TRIGGER (2005, 2008) and ENABLE TRIGGER (2005, 2008) commands.
Conditional deletion prevention
In this case, deletion should only be allowed under certain conditions. For example, we could allow to only delete single records:
CREATE TRIGGER [dbo].[Prevent_Foo_Multi_Delete]
ON [dbo].[Foo]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
IF (SELECT COUNT(ID) FROM deleted) > 1
RAISERROR('You can only delete a single Foo',1,0);
ELSE
DELETE Foo
FROM Foo INNER JOIN deleted ON Foo.ID = deleted.ID
Similarly, one could prevent the deletion of detail records to only a single master record by writing
IF (SELECT COUNT(DISTINCT BAR_ID) FROM deleted) > 1
Preventing Modifications
The same method can be used for UPDATE triggers. It may be, however, easier to define an ON UPDATE trigger to avoid rephrase the UPDATE statement in an INSTEAD OF trigger. In case of failure, we rollback the current transaction:
CREATE TRIGGER [dbo].[Prevent_Foo_Update]
ON [dbo].[Foo]
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF (SELECT COUNT(ID) FROM inserted) > 1 BEGIN
ROLLBACK TRANSACTION
RAISERROR('You can only modify 1 Foo',1,0);
END
END
Preventing Truncation
These mechanisms prevent you from an accidental UPDATE or DELETE on all records (e.g. by a missing WHERE clause, or semicolon in front of the WHERE condition).
However, there is still the TRUNCATE TABLE command which deletes all data in a table and cannot be stopped by a DELETE trigger:
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
The rescue shows in the preceding sentence:
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint
Simply have a table that references the tables to be protected:
CREATE TABLE Prevent_Truncate(
Foo_ID INT REFERENCES Foo(ID),
Bar_ID INT REFERENCES Bar(ID)
)
You only appreciate how valuable your data is once it’s lost 😉
devioblog 