Instead of triggers are used to reverse the default action of the query same as the word ‘insted of’ denotes. For example if we are going to execute query to delete record in Empoyee table and we want to first check whether id of employee passed Exist in database or not then we can place a “Insted of Trigger” in database on Delete Operation for that particular table and we will place code in trigger to first check whether employee with specified id exist in db . IF yes then delete that employe , otherwise abort the query execution.
In general language we use instead of triggers when we need to code - instead of doing xyz do abc . means we need to trigger the selection of operations in db depending on Values in database tables or other certain criteria.
INSTEAD OF TRIGGERS can be classified further into three types as:
- INSTEAD OF INSERT Trigger.
- INSTEAD OF UPDATE Trigger.
- INSTEAD OF DELETE Trigger.
EXAMPLE
Below trigger will prevent the deletion of records from the table where Emp_Sal > 1200. If such a record is deleted, the Instead Of Trigger will rollback the transaction, otherwise the transaction will be committed.
CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test]
INSTEAD OF DELETE
AS
declare @emp_id int;
declare @emp_name varchar(100);
declare @emp_sal int;
select @emp_id=d.Emp_ID from deleted d;
select @emp_name=d.Emp_Name from deleted d;
select @emp_sal=d.Emp_Sal from deleted d;
BEGIN
if(@emp_sal>1200)
begin
RAISERROR('Cannot delete where salary > 1200',16,1);
ROLLBACK;
end
else
begin
delete from Employee_Test where Emp_ID=@emp_id;
COMMIT;
insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());
PRINT 'Record Deleted -- Instead Of Delete Trigger.'
end
END
GO
