TRIGGERs

----------------------AFTER TRIGGER----------------

--create TABLE & INSERT records in it

CREATE TABLE Employee_Demo
(
 Emp_ID int identity,
 Emp_Name varchar(55),
 Emp_Sal decimal (10,2)
)
-- Now Insert records 
Insert into Employee_Demo values ('Amit',1000);
Insert into Employee_Demo values ('Mohan',1200);
Insert into Employee_Demo values ('Avin',1100);
Insert into Employee_Demo values ('Manoj',1300);

Insert into Employee_Demo values ('Riyaz',1400);

--create another TABLE for insert TRIGGER executions  
CREATE TABLE Employee_Demo_Audit
(
 Emp_ID int,
 Emp_Name varchar(55),
 Emp_Sal decimal(10,2),
 Audit_Action varchar(100),
 Audit_Timestamp datetime

)

--create  TRIGGER for executions

CREATE TRIGGER trgAfterInsert on Employee_Demo
FOR INSERT
AS 
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.'; 
insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER INSERT trigger fired.'

--Now Insert record
insert into Employee_Demo(Emp_Name,Emp_Sal)values ('suresh',1000);

--select statements

select * from Employee_Demo

select * from Employee_Demo_Audit