SQL | Instead Of Triggers
INSTEAD OF trigger is a DML Trigger
An INSTEAD OF trigger is a trigger that allows you to skip an INSERT, DELETE, or UPDATE statement to a table or a view and execute other statements defined in the trigger instead..
The actual insert, delete, or update operation does not occur at all.
CREATE TRIGGER [schema_name.] trigger_name
ON {table_name | view_name }
INSTEAD OF {[INSERT] [,] [UPDATE] [,] [DELETE] }
AS
{sql_statements}
Instead Of trigger can be of 3 types as below: -
INSTEAD OF INSERT Trigger
INSTEAD OF UPDATE Trigger
INSTEAD OF DELETE Trigger
let’s create a view handling multiple tables
CREATE VIEW vwEmployee
AS
SELECT emp.ID, emp.Name, Gender, Salary, dept.Name AS Department
FROM Employee emp
INNER JOIN Department dept
ON emp.DeptID = dept.ID
INSTEAD OF INSERT Trigger
An INSTEAD INSERT trigger allows you to skip an INSERT statement to a table or a view and execute other statements defined in the trigger instead.Lets try to insert a new record using above view
INSERT INTO vwEmployee VALUES(1, 'Deepak', 'Male', 90000, 'Developer')
-- When we execute the above query it gives us the error as 'View or function vwEmployee is not updatable because the modification affects multiple base tables.'
Lets Handle above situation using Instead of Insert Trigger
CREATE TRIGGER tr_vwEmployee_InsteadOfInsert
ON vwEmployee
INSTEAD OF INSERT
AS
BEGIN
DECLARE @DepartmenttId int
-- First Check if there is a valid Id in the Department Table
-- for the given Department Name
SELECT @DepId = dept.ID
FROM Department dept
INNER JOIN INSERTED inst
on inst.Department = dept.Name
--If the DepartmentId is null then throw an error
IF(@DepId is null)
BEGIN
RAISERROR('Invalid Department Name. Statement terminated', 16, 1)
RETURN
END
-- Now insert the data into the Employee table
INSERT INTO Employee(ID, Name, Gender, Salary, DeptID)
SELECT ID, Name, Gender, Salary, @DepartmenttId
FROM INSERTED
End
--Now, let's execute the below Insert statement.
INSERT INTO vwEmployee VALUES(1, 'Deepak', 'Male', 90000, 'Developer')
-- Instead Of Trigger inserts the row correctly into the Employee table as expected.
-- Since we are inserting a row, the inserted magic table will contain the newly added row whereas the deleted table will be empty.
INSTEAD OF UPDATE Trigger
An INSTEAD UPDATE trigger allows you to skip an UPDATE statement to a table or a view and execute other statements defined in the trigger instead.Lets try to update an existing record using above view
UPDATE vwEmployee
SET Name = 'Manoj', Department = 'HR' WHERE Id = 1
-- When we execute the above update query, we get the error as “View or function ‘vwEmployee ’ is not updatable because the modification affects multiple base tables.”
Lets Handle above situation using Instead of Update Trigger
CREATE TRIGGER tr_vwEmployee_InsteadOfUpdate
ON vwEmployee
INSTEAD OF UPDATE
AS
BEGIN
-- if EmployeeId is updated
IF(UPDATE(ID))
BEGIN
RAISERROR('Id cannot be changed', 16, 1)
RETURN
END
-- If Department Name is updated
IF(UPDATE(Department))
BEGIN
DECLARE @DepartmentID INT
SELECT @DepartmentID = dept.ID FROM Department dept INNER JOIN INSERTED inst ON dept.Name = inst.Department
IF(@DepartmentID is NULL )
BEGIN
RAISERROR('Invalid Department Name', 16, 1)
RETURN
END
UPDATE Employee set DeptID = @DepartmentID FROM INSERTED INNER JOIN Employee on Employee.ID = inserted.ID
End
-- If Name is updated
IF(UPDATE(Name))
BEGIN
UPDATE Employee SET Name = inserted.Name FROM INSERTED INNER JOIN Employee ON Employee.ID = INSERTED.ID
END
END
-- Now, let’s try to update Name and Department Name. The UPDATE query, works as expected, without raising the error –
INSTEAD OF DELETE Trigger
An INSTEAD DELETE trigger allows you to skip an DELETE statement to a table or a view and execute other statements defined in the trigger instead.Lets try to delete an existing ecord using above view
DELETE FROM vwEmployee WHERE ID = 1
-- When we execute the above query it gives us the error as ‘View or function vwEmployeeDetails is not updatable because the modification affects multiple base tables.‘
Lets Handle above situation using Instead of Delete Trigger
CREATE TRIGGER tr_vwEmployee_InsteadOfDelete
ON vwEmployee
INSTEAD OF DELETE
AS
BEGIN
-- Using Inner Join
DELETE FROM Employee emp
INNER JOIN DELETED del
ON emp.ID = del.ID
--OR
DELETE FROM Employee
WHERE ID IN (SELECT ID FROM DELETED)
END
-- Now, let’s try to delete record. The DELETE query, works as expected, without raising the error.
