Trigger in Sql Server

Trigger is a logic which executes on the tables after or before when the insertion, deletion and updation query executes. 

There are two type of trigger

1. Instead of trigger
2. After Trigger

Instead of trigger : - It fire before the insert/Update/Delete executes on table.

After Trigger : It fires After insert/delete/update command executes on table.

 Lets Start Understanding the use of trigger in sql server -
First we Create trigger : - this one is after trigger

 
CREATE Trigger tblUpdate   
On tblemp   
After Insert,Delete, Update   
As Begin   
   
Declare @Oldvalues nvarchar(50)   
Declare @newvalues nvarchar(50)   
   
--Fetch inserted value   
Select @newvalues=Customername from inserted   
--fetch deleted value   
   
Select @Oldvalues=Customername from deleted   
   
insert into tblaudit(LastUpdated,Oldvalues,newvalues)   
values(getdate(),@Oldvalues,@newvalues)   
   
End   


Description of Used word in trigger : -
tblUpdate - Trigger Name
tblemp     - Table name on which trigger will fire.
Inserted  - When any insert command executes it will store the current value.

Deleted   - When any delete command excutes it will store the deleted data.


Here i have created two table one is tblemp and tblaudit
Table tblaudit


CREATE TABLE [dbo].[tblaudit](
    [LastUpdated] [datetime] NULL,
    [Oldvalues] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [newvalues] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Table tblemp

CREATE TABLE [dbo].[tblemp](
    [CustomerID] [int] IDENTITY(1,1) NOT NULL,
    [Customername] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

When any insert update delete command executes trigger will fire automatically..

Note : First create table then trigger.
Watch Video click here