Prevent Hard Delete with Trigger in SQL Server

Do we know that a single DELETE query can destroy millions of production records in seconds? In banking, healthcare, or finance — that’s unacceptable! In the data ware house, we have soft deleted the records it means we are updating flags to identify the deleted records or inactive records. There also we do not have control to prevent to run the delete command. If some by mistake run the delete command then we can loss the data.

Suppose if we have a requirement that no one can delete the records from the table. If someone want to delete the record from this table we will track and log this information into log table and prevent the delete.

We can archive this by using trigger. 

Let’s see the demo

Here we are creating the audit table and main table.

--Main table CREATE TABLE Emptbl (     EmpId INT PRIMARY KEY,     EmpName VARCHAR(50),     Salary INT,     IsActive BIT DEFAULT 1 );   --Audit table CREATE TABLE EmpDeleteAudit(     Id INT IDENTITY(1,1) PRIMARY KEY,     AuditMessage VARCHAR(500),     createdBy SYSNAME  DEFAULT         SYSTEM_USER,     createdOn DATETIME DEFAULT        CURRENT_TIMESTAMP );

Both tables created successfully. Now creating trigger on the main table.

CREATE TRIGGER tr_preventdelete_emptbl ON emptbl FOR DELETE AS   BEGIN       INSERT INTO empdeleteaudit                   (auditmessage)       SELECT 'Delete operation blocked. User : '              + Suser_sname()              + ' Trying to delete record which EmpID '              + Cast(empid AS VARCHAR(20)) + ' on '              + Cast(Getdate() AS VARCHAR(30))       FROM   deleted;         RAISERROR('Delete operation blocked. Instead of hard delete use soft delete                  update IsActive flag to 0. Data is logged in Audit Table.',16,1       );     ROLLBACK TRANSACTION;   END;

 

Inserting few records into the main table

--inserting few records   insert into Emptbl (EmpId,EmpName,Salary) values   (1,'Bagesh',10000),(2,'Amit',20000)

See the records in the table

Now we are trying to delete the records from this table.

Delete from Emptbl where EmpId=1

Getting the error

See the Audit log table

See the records in the main table

No record deleted.