Trigger on Delete with Example in SQL

Trigger on Delete with Example in SQL

In my Previous post i wrote on Triggers and showed an example of how DML Trigger Insert works.
In this post  will show how an Example of DML Delete trigger.
In case you want to know about DML Insert Trigger Read my Previos post .

http://sqlandssrssolutions.blogspot.in/2014/01/trigger-on-insert-with-example-in-sql.html

Consider a scenario where you want to have keep track of  deleted records from a table like an Audit table so the audit table will capture  the deleted values which occured in the base table.

--Base Table Script with some Records

CREATE TABLE [dbo].[EmployeeBasicDetails](
 [Empid] [int] NOT NULL,
 [EmpName] [nvarchar](50) NULL,
 [EmpSalary] [float] NULL
 ,Emp_UserName nvarchar(100),
 Emp_Password nvarchar(10)
) ON [PRIMARY]
GO


INSERT [dbo].[EmployeeBasicDetails] ([Empid], [EmpName], [EmpSalary],Emp_UserName,Emp_Password) VALUES (1, N'sumit', 5000,'Sumit_1','1235')
INSERT [dbo].[EmployeeBasicDetails] ([Empid], [EmpName], [EmpSalary] ,Emp_UserName,Emp_Password) VALUES (4, N'amit', 9000,'amit_4','121')
INSERT [dbo].[EmployeeBasicDetails] ([Empid], [EmpName], [EmpSalary],Emp_UserName,Emp_Password) VALUES (5, N'vijay', 10000,'vijay-5','111')
INSERT [dbo].[EmployeeBasicDetails] ([Empid], [EmpName], [EmpSalary],Emp_UserName,Emp_Password) VALUES (7, N'suresh', 5000 ,'suresh-7','120')
INSERT [dbo].[EmployeeBasicDetails] ([Empid], [EmpName], [EmpSalary],Emp_UserName,Emp_Password) VALUES (9, N'Mukesh', 130,'mukesh-9','132')

select * from [EmployeeBasicDetails]


--Audit table script which will store Deleted values from Employee Basic Detail Table.

CREATE TABLE [dbo].[EmployeeBasicDetails_Audit](
 [Empid] [int] NOT NULL,
 [EmpName] [nvarchar](50) NULL,
 [EmpSalary] [float] NULL
 ,Emp_UserName nvarchar(100),
 Emp_Password nvarchar(10)
) ON [PRIMARY]
GO

--Script of DML Delete Trigger that generate an action and put all the deleted values in EmployeeAudit Table

Create trigger T_delete on [EmployeeBasicDetails]
for delete
as begin
insert into [EmployeeBasicDetails_Audit] 
select * from deleted  
end


Now to test trigger we will delete a record from EmployeeBasicDetails table

delete from EmployeeBasicDetails where EmpName ='sumit'

--verifty Result
--Deleted row moved to Audit Table

Select * from [EmployeeBasicDetails_Audit] 

OUTPUT:

Empid EmpName EmpSalary Emp_UserName Emp_Password
1       sumit                   5000                  Sumit_1                   1235

--No Record with EmpName Sumit in Base Table
select * from [EmployeeBasicDetails] where EmpName ='sumit'

NOTE : In SQL 2008 Microsoft has introduced Change Data Capture to capture all the DML operations(Insert,Update and Delete) happend in the Master or baseTables.
We can use CDC also to keep track of Changes done in Master or base tables.