Prevent Hard Delete instead of Delete do Soft 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 due to the that we are doing the soft delete even user run the delete command.

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 Emp_SD_tbl (     EmpId INT PRIMARY KEY,     EmpName VARCHAR(50),     Salary INT,     IsActive BIT DEFAULT 1     DeletedBy varchar(50) null,     DeletedOn Datetime null );

Table created successfully. Now creating trigger on the table.

CREATE TRIGGER tr_preventdelete_emp_sd_tbl ON emp_sd_tbl instead OF DELETE AS   BEGIN       UPDATE e       SET    isactive = 0,              deletedby = SYSTEM_USER,              deletedon = CURRENT_TIMESTAMP       FROM   emp_sd_tbl e              INNER JOIN deleted d                      ON e.empid = d.empid;   END;

 Inserting few records into the main table

--inserting few records   insert into emp_sd_tbl (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 records in the table

No record deleted. Instead of deleting the record it has update the record. It means did soft delete.