Sql Server - INSTEAD OF TRIGGER

INSTEAD OF TRIGGER -

 It is used to perform your desired operations instead of INSERT/UPDATE/DELETE on table or view by making use of magic tables which gets populated as a result of DML operations.

EXAMPLE 1:
CREATE DATABASE TEST_INSTEAD_OF_TRG
GO

USE TEST_INSTEAD_OF_TRG
GO

inserting into view which has two or more tables joined using INSTEAD OF TRIGGER

-- Create two tables
-- student table

CREATE TABLE STUDENT(
ID INT,
Name VARCHAR(20))
GO
 
INSERT INTO STUDENT SELECT 1,'sathya'
GO

--department table

CREATE TABLE DEPARTMENT(
ID INT,
DEPT_Name VARCHAR(20))
GO
 
INSERT INTO DEPARTMENT SELECT 1,'ECE'
GO

--create view joining student & department table

CREATE VIEW VW_INSTEAD_OF_TRG
AS
SELECT a.ID,a.Name,b.DEPT_Name FROM STUDENT a 
JOIN DEPARTMENT b ON a.ID = b.ID

-- try to insert into view
INSERT INTO VW_INSTEAD_OF_TRG SELECT 2,'deepak','CS'
GO

you will get the below error message,because you cannot insert into view with two or more tables joined

Msg 4405, Level 16, State 1, Line 1
View or function 'VW_INSTEAD_OF_TRG' is not updatable because the modification affects multiple base tables.

--solution,create instead of trigger,as shown below

CREATE TRIGGER INSTEAD_OF_TRG ON VW_INSTEAD_OF_TRG
INSTEAD OF INSERT
AS
INSERT INTO STUDENT (ID,Name) SELECT ID,Name FROM INSERTED
INSERT INTO DEPARTMENT (ID,DEPT_Name) SELECT ID,DEPT_Name FROM INSERTED

--Again try to insert
INSERT INTO VW_INSTEAD_OF_TRG SELECT 2,'deepak','CS'
GO

--you will notice records inserted into view with joined tables
 
SELECT * FROM VW_INSTEAD_OF_TRG
SELECT * FROM STUDENT
SELECT * FROM DEPARTMENT

EXAMPLE 2:to avoid default values in table getting updated/inserted 

--create table with default value option  for few columns

CREATE TABLE Login_Audit (
ID INT,
NAME VARCHAR(20),
Last_Login_User VARCHAR(20) DEFAULT SYSTEM_USER,
Last_Login_Date DATE DEFAULT GETDATE())
GO

--try to populate the table (including default value column)  with values
 
INSERT INTO Login_Audit (ID,NAME,Last_Login_User,Last_Login_Date) SELECT 1,'Sathya','Hacker',GETDATE()-365
GO

--If you run the below query,you will notice default values getting updated
SELECT * FROM Login_Audit

--Solution,create instead of trigger,as shown below

CREATE TRIGGER INSTEAD_OF_TRGG ON Login_Audit
INSTEAD OF INSERT
AS
INSERT INTO Login_Audit (ID,NAME) SELECT ID,NAME FROM inserted

--try again to override the default values

INSERT INTO Login_Audit (ID,NAME,Last_Login_User,Last_Login_Date) SELECT 1,'Sathya','Hacker',GETDATE()-365
GO

--you will notice ,instead of trigger has prevented default values getting updated
SELECT * FROM Login_Audit

DROP DATABASE TEST_INSTEAD_OF_TRG