Trigger

Trigger Is One Kind Of StoreProcedure that automatically Execute when DML or DDL Statement Associated with the trigger is executed. DML Triggers are used to evaluate data after data manipulation using DML statements.   
*1 After Trigger (For Trigger)

This Trigger fire After SQL server completes the execution of the action successfully that fired it.

Example: - If you insert/Update/Delete record on table trigger associated that command and then change the respective table but when insert/Update/Delete fail After Trigger is not Fired

After Insert Trigger
After Update Trigger
After Delete Trigger

**2 Instead Of Trigger

This Trigger Fire Before SQL Server completes the execution of the action.Example: - If you insert/Update/Delete record on table trigger associated that command and then change the respective table but when insert/Update/Delete fail Instead Trigger is Fired.

Instead, Insert Trigger
Instead, Update Trigger
Instead, Delete Trigger

---First Create Table tblEmployee2
create table tblEmployee2
(
Employee_Id int,
Employee_Name nvarchar(50),
Employee_Salary decimal(10,2)
)

---Insert Record Into Table tblEmployee2
Insert into tblEmployee2 values (1,'Ben',2000)
Insert into tblEmployee2 values (2,'Clark',1200)
Insert into tblEmployee2 values (3,'John',3500)
Insert into tblEmployee2 values (4,'Sam',4000)
Insert into tblEmployee2 values (5,'Ponting',5000)

Now Create Table tblEmployee2_Audit for logging/Backup purpose of Table tblEmployee2 
------Create Table tblEmployee2_Audit

create table tblEmployee2_Audit
(
Employee_Id int,
Employee_Name nvarchar(50),
Employee_Salary decimal(10,2),
Audit_Action nvarchar(50),
Audit_Timestamp datetime
)

After Insert Trigger
---create trigger on tblEmployee2 for Insert statement

create trigger trgAfterInsert on tblEmployee2
for insert
as 
declare @Employee_Id int,
@Employee_Name nvarchar(50),
@Employee_Salary decimal(10,2),
@Audit_Action varchar(100);

select @Employee_Id= i.Employee_Id from inserted i;
select @Employee_Name= i.Employee_Name from inserted i;
select @Employee_Salary= i.Employee_Salary from inserted i;

set @Audit_Action = 'Inserted Record, After Trigger';

insert into tblEmployee2_Audit(Employee_Id,Employee_Name,Employee_Salary,Audit_Action,Audit_Timestamp)
values (@Employee_Id,@Employee_Name,@Employee_Salary,@Audit_Action,GETDATE());

Print 'After Trigger Is Fired ';


Now Try To Insert Data in tblEMployee2 Table

insert into tblEMployee2(Employee_Id, Employee_Name, Employee_Salary)  values (6,'Pam',5200)


---------------**********----------------

---After Update Trigger------

--Create trigger on tblEMployee2 for update statement
Create trigger trgAfterUpdate on tblEMployee2
for update
as
declare @Employee_Id int,
@Employee_Name nvarchar(50),
@Employee_Salary decimal(10,2),
@Audit_Action varchar(100);

select @Employee_Id= i.Employee_Id from inserted i;
select @Employee_Name= i.Employee_Name from inserted i;
select @Employee_Salary= i.Employee_Salary from inserted i;
if update(Employee_Name)
         set @Audit_Action = 'Update Record---After update trigger';
else
        set @Audit_Action = 'Update Record --After update trigger';

Insert into tblEmployee2_Audit(Employee_Id,Employee_Name,Employee_Salary,Audit_Action,Audit_Timestamp)
values (@Employee_Id,@Employee_Name,@Employee_Salary,@Audit_Action,GETDATE());

Print 'After Update Trigger Fired';

Now try to update data in tblEMployee2 table
update tblEMployee2 set Employee_Name='Paul' where Employee_Id = 2

-------******************-------------------

select * from tblEmployee2
select * from tblEmployee2_Audit