SQL Server Instead of Delete Trigger to Prevent Data Deletion

SQL Server database administrators or SQL programmers use Instead of Delete trigger to prevent data deletion from database tables like look-up table or master data table. In this SQL tutorial, I want to share how to create a SQL Server Instead of Delete trigger on a database table which prevents accidentally data deletions by executing "Delete From table" command.

Although defining foreign keys from related database tables by referencing master data tables avoids data loss from master table used in those related transaction tables. But if the master table row is not referred from any other table, a Delete command will successfully remove that record from master table. We will create Instead of Delete trigger which will help SQL Server administrators and developers to prevent master tables from accidental data losses.

Following SQL script will create sample database table for SQL developers and database administrators to apply suggested solution upon in this SQL tutorial. Use the below sample Create Table script to create new table in your SQL database with sample data formed of a single row.


create table lookupdata (

 id int identity(1,1),

 code varchar(5),

 value varchar(100)

)

insert into lookupdata select 'ey','kodyaz.com'

Code

If a user execute SQL Delete command on a database table, or an unauthorized user targets and executes Delete From Table command on purpose, database administrators face the risk of losing valuable data stored in that database table.

For example, using SQL DELETE statement will remove our sample data record from SQL Server lookup table.


delete from lookupdata

-- select * from lookupdata

Code

Prevent Delete using Instead of Delete Trigger on a Database Table

Let's now try to prevent data deletion from SQL Server database table by using an SQL trigger.
We have to create Instead of Delete trigger on SQL table lookupdata using the following "CREATE TRIGGER" syntax


create trigger tr_lookupdata_del on lookupdata instead of delete

as

declare @i bit

go

Code

SQL Server database table Instead of Delete trigger

Here is the database trigger displayed on SQL Server Management Studio (SSMS) Object Explorer window under Triggers node of the database table

SQL Server Instead of Delete trigger on SSMS Object Explorer

Actually the SQL "Instead of Trigger" will run the SQL code included in the database trigger code instead of the DELETE command which the user is executed. Since as database developer or as a DBA, our aim in this SQL Server tutorial is to prevent users to delete data records from database table, we will not execute any meaningful command within this trigger.

Because of preventing SQL engine to execute any DML (Data Manipulation Language) command, I only declare a bit variable within the SQL Server Instead of Trigger code.

After the creation of Instead of Delete trigger on SQL Server database table, you can run Delete commands to test if we could prevent data deletion from table. Of course first populate master table with some test data since we have already deleted all rows stored in the table during previous Delete command execution


-- insert into lookupdata select 'ey','kodyaz.com'

delete from lookupdata

-- select * from lookupdata

Code

Although in Query Editor screen the outcome of the Delete statement is as "Query executed successfully" and as "1 row(s) affected" no rows is actually deleted or removed from database table.

If you run a SELECT all query from master lookup table, you will see no rows are deleted from the database table

Perhaps for audit purposes, you can add codes to log who executed which DELETE command and when within the Instead of Delete trigger instead of dummy variable declaration code line

How to Avoid from Truncate Table and Drop Table

What is the limitation of Instead of Delete trigger to prevent a user to delete records from a master or look up table? Instead of Delete trigger does not halpe you avoid data loss due to SQL Truncate Table command or Drop Table command. Unfortunately if a user connected to the database executes Drop Table or Truncate Table commands, the Instead of Delete trigger cannot help database administrators to keep table data from vanishing.

To prevent data loss due to unauthorized or accidental SQL Server Truncate Table command, SQL professionals can use Foreign Key Constraint against Truncate Table

To prevent your database table being dropped, database administrators can use one of the following methods. Please refer to the referenced SQL tutorials for each method which shows details on avoiding drop table execution against harmful acts and accidental deletions.

One of the methods that I can suggest for SQL Server administrators is to create SQL view with SchemaBinding to prevent Drop Table execution on a specific data table.

An other major method which is available for SQL Server 2005 and later versions is to prevent DROP Table using SQL Server DDL trigger. A DDL trigger in other words Data Definition Language trigger is a new type of SQL Server trigger defined on database objects and works on Create, Drop, Alter, etc commands changing the database or SQL Server object instead of the data in it. DDL triggers also provide useful and informative data about what is being executed by whom and when bu EVENTDATA function. Database professionals should use EventData for logging changes on SQL Server objects for audit and security of their database servers.