Triggers in SQL Server - After

Triggers are very useful when you want to track events occurring in Database. Let us discuss important triggers in SQL Server :

AFTER and INSTEAD OF;

1.) AFTER TRIGGERS:

AFTER UPDATE :

create trigger update_data ON dbo.employee
FOR UPDATE
AS
Declare @empID int;
Declare @Name varchar(15);
Declare @Salary Decimal (12,4);
Declare @Action Varchar(100);

Select @empId = e.empId from inserted e;
Select @Name = e.Name from inserted e;
Select @Salary = e.Salary from inserted e;
SET @Action = 'After Update Trigger used';

insert into Employee_Log
(EMPID ,  name , salary , Action , Date_Time)
VALUES (@empID , @Name, @Salary, @Action, getdate());

PRINT 'After Update Trigger Used'
GO


AFTER INSERT:

create trigger INSERT_data ON dbo.employee
FOR INSERT
AS
Declare @empID int;
Declare @Name varchar(15);
Declare @Salary Decimal (12,4);
Declare @Action Varchar(100);

Select @empId = e.empId from inserted e;
Select @Name = e.Name from inserted e;
Select @Salary = e.Salary from inserted e;
SET @Action = 'After INSERT Trigger used';

insert into Employee_Log
(EMPID ,  name , salary , Action , Date_Time)
VALUES (@empID , @Name, @Salary, @Action, getdate());

PRINT 'After INSERT Trigger Used'
GO


AFTER DELETE:

create trigger DELETE_data ON dbo.employee
FOR DELETE
AS
Declare @empID int;
Declare @Name varchar(15);
Declare @Salary Decimal (12,4);
Declare @Action Varchar(100);

Select @empId = e.empId from DELETEd e;
Select @Name = e.Name from DELETEd e;
Select @Salary = e.Salary from DELETEd e;
SET @Action = 'After DELETE Trigger used';

Insert into Employee_Log
(EMPID ,  name , salary , Action , Date_Time)
VALUES (@empID , @Name, @Salary, @Action, getdate());

PRINT 'After DELETE Trigger Used'
GO  'After DELETE Trigger Used'
GO




update  dbo.employee
set Name = 'Sunny'
where empID = 6;

insert into employee (EMPID,Name, Salary)
Values ('7','Atanu',600);

delete from employee
where empid = '5';