Triggers in SQL SERVER - Instead of

Instead of trigger is used to perform an action different from what was fired.







CREATE TRIGGER instead_of_update ON dbo.employee
INSTEAD OF 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 = 'Instead of Update Trigger used';

BEGIN
RAISERROR('Cannot update name',10,1);
ROLLBACK;
insert into Employee_Log
(EMPID ,  name , salary , Action , Date_Time)
VALUES (@empID , @Name, @Salary, @Action, getdate());

PRINT 'Instead Of update Trigger used'
END
GO


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

CREATE TRIGGER instead_of_delete ON dbo.employee
INSTEAD OF 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 = 'Instead of Delete Trigger used';

BEGIN
RAISERROR('Cannot delete record',16,1);
ROLLBACK;
insert into Employee_Log
(EMPID ,  name , salary , Action , Date_Time)
VALUES (@empID , @Name, @Salary, @Action, getdate());

PRINT 'Instead Of delete Trigger used'
END
GO

delete from employee

where empid = '1';