INSTEAD OF Triggers
A trigger which gets executed automatically in place of triggering actions i.e., INSERT, DELETE and UPDATE is called an INSTEAD OF trigger.
INSTEAD OF triggers gets executed automatically before the Primary Key and the Foreign Key constraints are checked, whereas the traditional AFTER triggers gets executed automatically after these constraints are checked.
CREATE TRIGGER trgAfterInsert
On User_Details
FOR INSERT
AS
BEGIN
Print (‘AFTER Trigger [trgAfterInsert] – Trigger executed !!’)
END
INSERT INTO USER_DETAILS(USERID, FNAME,LNAME, MNAME, EMAIL)
VALUES(100, ‘FName’,'LName’,'MName’,'test@test.com’)
Output
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint ‘FK_User_Details_User_Master’. The conflict occurred in database ‘KDMNN’, table ‘User_Master’, column ‘UserID’.
The statement has been terminated.
UserID 100 does not exist in the User_Master table, so the Foreign Key constraint has been checked and an error message is displayed. What we can conclude is: AFTER triggers gets executed automatically after the PK and FK constraints.
Create Trigger trgInsteadInsert
On User_Details
INSTEAD OF INSERT
AS
BEGIN
Print (‘INSTEAD OF Trigger [trgInsteadInsert] – Trigger executed !!’)
END
INSERT INTO USER_DETAILS(USERID, FNAME,LNAME, MNAME, EMAIL)
VALUES(100, ‘FName’,'LName’,'MName’,'test@test.com’)
Output
INSTEAD OF Trigger [trgInsteadInsert] – Trigger executed !!
(1 row(s) affected)
Even if the UserID 100 does not exists in the User_Master table, the trigger gets gets executed automatically.
DROP Trigger trgInsteadInsert
Unlike AFTER triggers, INSTEAD OF triggers can be created on views.
Create trigger trgOnView
on vwUserMaster
INSTEAD OF INSERT
AS
begin
Print (‘INSTEAD OF Trigger [trgOnView] – vwUserMaster !!!’)
End
INSERT INTO VWUSERMASTER(USERNAME, PASSWORD)
VALUES(‘Damerla’,'Venkat’)
Output
INSTEAD OF Trigger [trgOnView] – vwUserMaster !!
(1 row(s) affected)
So whenever a user tries to insert data into the view vwUserMaster, the INSTEAD OF trigger trgOnView will automatically be executed.
In SQL SERVER 2000, views can be used to INSERT/DELETE and UPDATE the data in the multiple tables, and this can be achieved using INSTEAD OF triggers.
CREATE VIEW vwUser
AS
SELECT
[User_Master].[Username],
[User_Master].[Password],
[User_Details].[FName],
[User_Details].[MName],
[User_Details].[LName],
[User_Details].[Email]
FROM
[User_Master], [User_Details]
WHERE
[User_Master].[UserID]=[User_Details].[UserID]
CREATE TRIGGER tgrInsertData
ON vwUser
INSTEAD OF INSERT
AS
BEGIN
Declare @UserName varchar(50)
Declare @Password varchar(50)
Declare @FName varchar(50)
Declare @MName varchar(50)
Declare @LName varchar(50)
Declare @Email varchar(50)
SELECT
@UserName = UserName,
@Password = Password,
@FName = FName,
@MName = MName,
@LName = LName,
@Email = Email
FROM INSERTED
INSERT INTO User_Master(UserName, Password)
VALUES(@UserName, @Password)
INSERT INTO User_Details(UserID,FName,LName,MName,Email) VALUES(@@Identity, @FName, @LName, @MName, @Email)
END
INSERT INTO vwUser(UserName,Password,FName,LName,MName,Email)
VALUES (‘Dhananjay’,'Dhananjay’,'Dhananjay’,'Nagesh’,NULL,
‘Dhananjay@kdmnn.com’
Output
AFTER Trigger [trgInsert] – Trigger executed !!
AFTER Trigger [trgInsert2] – Trigger executed !!
AFTER Trigger [trgInsert3] – Trigger executed !!
AFTER Trigger [trgEncrypted] Encrypted – Trigger executed !!
(1 row(s) affected)
AFTER Trigger [trgAfterInsert] – Trigger executed !!
(1 row(s) affected)
Then check the data in the following tables User_Master and User_Details. The new row gets inserted in both the tables.
A view or table can have only one INSTEAD OF trigger for each INSERT, UPDATE and DELETE events.
We have seen that you can create any number of AFTER triggers on the table for the same event, whereas you cannot do that with INSTEAD OF triggers.
CREATE TRIGGER trgInsteadOfTrigger1
ON vwUserMaster
INSTEAD OF UPDATE
AS
BEGIN
Print (‘INSTEAD OF Trigger [trgInsteadOfTrigger1] – Trigger executed !!’)
END
CREATE TRIGGER trgInsteadOfTrigger2
ON vwUserMaster
INSTEAD OF UPDATE
AS
BEGIN
Print (‘INSTEAD OF Trigger [trgInsteadOfTrigger2] – Trigger executed !!’)
END
Output
Server: Msg 2111, Level 16, State 1, Procedure trgInsteadOfTrigger2, Line 6
Cannot CREATE trigger ‘trgInsteadOfTrigger2′ for view ‘vwUserMaster’ because an INSTEAD OF UPDATE trigger already exists.
From the output, it is clear that you cannot create two INSTEAD OF triggers on the view/ table for the same event.
Note: An important point to be noted is that INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on tables that have corresponding ON DELETE or ON UPDATE cascading referential integrity defined.
At last, how would you know what are the triggers associated with the table and what type of the trigger it is? Whether AFTER or INSTEAD OF?
The solution for this question is sp_helptrigger. This stored procedure gives all the information about the triggers such as Event on which the trigger gets executed, the type of the trigger, etc.
Sp_helptrigger User_Master
Output
trigger_name trigger_owner isupdate isdelete isinsert isafter isinsteadof
trgInsert dbo 0 0 1 1 0
trgInsert2 dbo 0 0 1 1 0
trgInsert3 dbo 0 0 1 1 0
trgEncrypted dbo 0 0 1 1 0
Triggers can be used in the following scenarios, such as if the database is de-normalized and requires an automated way to update redundant data contained in multiple tables, or if customized messages and complex error handling are required, or if a value in one table must be validated against a non-identical value in another table.
Triggers are a powerful tool that can be used to enforce the business rules automatically when the data is modified. Triggers can also be used to maintain the data integrity. But they are not to maintain data integrity. Triggers should be used to maintain the data integrity only if you are unable to enforce the data integrity using CONSTRAINTS, RULES and DEFAULTS. Triggers cannot be created on the temporary tables.
