SQL Server -Triggers(After Insert,After Update,After Delete) & Instead Of Trigger

 -- First create table Employee_Demo
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);
go
create table Employee_Demo_Audit
(Emp_ID INT,
Emp_Name varchar(55),
Emp_Sal decimal(10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime)
go
---AFTER INSERT Trigger
Create trigger Employee_Demo_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 fired'
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.'
go
insert into Employee_Demo(Emp_Name,Emp_Sal)values ('Shailu',1000);

select * from Employee_Demo
select * from Employee_Demo_Audit

go
--AFTER Delete Trigger
Create trigger Employee_Demo_TrgAfterDelete on Employee_Demo
for Delete
as
declare @empid int,@empname varchar(55),@empsal decimal(10,2),@audit_action varchar(100)
select @empid=i.emp_id from Deleted i;
select @empname=i.emp_name from Deleted i;
select @empsal=i.emp_sal from Deleted i;
set @audit_action='Deleted the Record ---After Delete Trigger fired'
insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,GETDATE())
print 'AFTER Delete Trigger fired.'
go
insert into Employee_Demo(Emp_Name,Emp_Sal)values ('Shailu',1000);
delete from Employee_Demo where Emp_ID=6
select * from Employee_Demo
select * from Employee_Demo_Audit


go
---After Update trigger
create trigger Employee_Demo_Trgafterupdate on employee_Demo
for update
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;
if update(emp_name)
set @audit_action='Update the emp_name record --After Update trigger fired.'
if update (emp_sal)
set @audit_action='Update the emp_sal record --after update trigger fired'

insert into employee_demo_audit(emp_id,emp_name,emp_sal,audit_action,audit_timestamp)
valueS(@empid,@empname,@empsal,@Audit_action,getdate())
print 'After Update trigger fired.'

go
select * from employee_demo
select * from employee_demo_audit
go
update employee_demo set emp_name='Reddeppa' where emp_name='Amit'
go



---Insetad of triggers
Create trigger InsteadOftrigger_EmployeeDemo_Insert on employee_demo
instead of Insert
as
declare @emp_id int,@emp_name varchar(55),@emp_sal decimal(10,2),@audit_action varchar(100);
select @emp_id=i.emp_id from inserted i;
select @emp_name=i.emp_name from inserted i;
select @emp_sal=i.emp_sal from inserted i;
set @audit_action='Iserted the record --Instead Of Insert trigger Fired.'
begin
begin tran
set nocount on
if(@emp_sal <=1000)
begin
raiserror('Cannot insert where salary <1000',16,1)
rollback
end
else
begin
insert into employee_demo_audit(emp_id,emp_name,emp_sal,audit_action,audit_timestamp)
values(@@identity,@emp_name,@emp_sal,@audit_action,getdate())
commit tran
print 'Record inserted ---Instead of trigger fired.'
end
end
go
insert into Employee_Demo values ('Shailu',1300)
insert into Employee_Demo values ('Shailu',900)
go
select * from employee_demo
select * from employee_demo_audit