Trigger on Insert with Example in SQL

Trigger on Insert with Example in SQL

 A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event.

 DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

 We can have Triggers in Both DML as DDL Operations.

There are two type of Triggers.

1-After Triggers-(After Insert,After Delete and After Update)
2-Instead of Triggers-(Instead of Insert,Instead of Delete and Instead of Udpate)

 Below is the Example of DML after Insert Trigger with Insert Action fires an automatic event which will insert into another table.

 Like  we have two tables Employee basic Details and EmployeeUserDetails and we want like whenever we will insert any Records in EmployeeBasicDetails
 at the same time it will insert the EmployeeUserName and Password in EmployeeUserDetails Table. As we want EmployeeUserName and Password column in both the Table.


 --EmployeeBasicDetails Table Script
 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

--Employee User Details Table
CREATE TABLE [dbo].[EmployeeUserDetails](
 Emp_UserName nvarchar(100),
 Emp_Password nvarchar(10)
) ON [PRIMARY]
GO

SO we want whenever we add an employee with all his details in EmployeeBasicDetails Table a Trigger fires and it will save the EmpuserName and Password into EmployeeUserDetails table

--Trigger on insert 

Create trigger T_insert on [EmployeeBasicDetails]
for insert
as begin
Declare @EmpUserName nvarchar(100)
Declare @Emppassword nvarchar(100)
select @EmpUserName =Emp_UserName,@Emppassword= Emp_Password from inserted 
insert into [EmployeeUserDetails] values(@EmpUserName,@Emppassword)
end

Now we will insert records in EmployeeBasicDetails table and at the same time trigger will insert EmpuserName and Password into EmployeeUserDetails table.

--inserting Records in EmployeeBasicDetails which will fire an insert event in --EmployeeUserdetails tables also

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')

--verify Result

select * from [EmployeeBasicDetails]

Output:
Empid EmpName EmpSalary Emp_UserName Emp_Password
1 sumit 5000 Sumit_1 1235
4 amit 9000 amit_4 121
5 vijay 10000 vijay-5 111
7 suresh 5000 suresh-7 120
9 Mukesh 130 mukesh-9 132


Select * from [EmployeeUserDetails]

Output:
Emp_UserName Emp_Password
Sumit_1 1235
amit_4 121
vijay-5 111
suresh-7 120
mukesh-9 132

So everytime any insert operation will happen in EmployeeBasicDetails table it will fire the Trigger to insert UserName and Password in Userdetails table too.

NOTE: Inserted and Deleted tables used in Triggers are termed as Magic Tables.