Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Using INSTEAD OF triggers in SQL Server for DML operations

MSSQLTips author Atif Shehzad By:   |   Read Comments (18)   |   Related Tips: More > Triggers

Problem
I have created some views to provide limited data access for an application. Several of these views are composed of more than one table and there are problems while performing insert, delete, or update operations on multi-base table views. I am required to seamlessly manage such problems, so that developers do not get direct access to the base tables for DML operations. In SQL Server 2000 and onwards there are INSTEAD OF triggers that can be used to carry out such tasks. Although these types of triggers can be used in a number of scenarios their primary function is to perform DML operations through such views. It looks like a powerful capability and I am interested to know about the implementation of INSTEAD OF triggers for various DML operations.

Solution
In SQL Server 2000 and later versions there are two types of DML triggers

  • AFTER triggers
  • INSTEAD OF triggers

Both of these work for insert, delete and update operations. Triggers created with FOR or AFTER keywords are both AFTER triggers. AFTER triggers do not work for views, so we will discuss the properties and functionality of INSTEAD OF triggers.

INSTEAD OF triggers cause their source DML operation to skip and they just execute the code provided inside them. Actual insert, delete or update operation do not occur at all. However they have their associated inserted and deleted tables simulating the DML operation. Inserted and deleted tables are widely used in operations inside triggers and I will show you some examples below . We will discuss further aspects of INSTEAD OF triggers by going through some examples for DML operations.


SETUP

In the following script we will create and populate two base tables and a create a view over both tables.

Script #1: Create and populate base tables and create view on these
USE AdventureWorks
GO
-- Create table for employees
CREATE TABLE Employees 
(EmpCode VARCHAR(8) PRIMARY KEY, Name VARCHAR(50) NOT NULL, 
Designation VARCHAR(50) NOT NULL, QualificationCode TINYINT, 
Deleted BIT NOT NULL DEFAULT 0)
GO
-- Create look up table for employees qualification
CREATE TABLE Lib_Qualification 
(QualificationCode TINYINT PRIMARY KEY, Qualification VARCHAR(20) NOT NULL)
GO
-- Add constraint to lib_qualification
ALTER TABLE dbo.Lib_Qualification ADD CONSTRAINT
FK_Lib_Qualification_Lib_Qualification FOREIGN KEY
( QualificationCode ) REFERENCES dbo.Lib_Qualification
( QualificationCode ) ON UPDATE NO ACTION 
ON DELETE NO ACTION 
GO 
-- Add constraint to employees 
ALTER TABLE dbo.EMPLOYEES ADD CONSTRAINT
FK_EMPLOYEES_Lib_Qualification FOREIGN KEY
( QualificationCode ) REFERENCES dbo.Lib_Qualification
( QualificationCode ) ON UPDATE NO ACTION 
ON DELETE NO ACTION 
GO
-- Insert data into lib_qualification table
Insert into lib_qualification VALUES (1, 'MS')
Insert into lib_qualification VALUES (2, 'MCS')
Insert into lib_qualification VALUES (3, 'BCS')
Insert into lib_qualification VALUES (4, 'MBA')
GO
-- Insert data into employees table
Insert into Employees VALUES ('405-21-1' ,'Emp1' ,'Designation1' ,1 ,0)
Insert into Employees VALUES ('527-54-7' ,'Emp2' ,'Designation2' ,2 ,0)
Insert into Employees VALUES ('685-44-2' ,'Emp3' ,'Designation3' ,1 ,0)
Insert into Employees VALUES ('044-21-3' ,'Emp4' ,'Designation4' ,3 ,0)
Insert into Employees VALUES ('142-21-9' ,'Emp5' ,'Designation5' ,2 ,0)
GO
-- Create view by two base tables
CREATE VIEW vw_EmpQualification
AS
SELECT EmpCode, Name, Designation, Qualification
FROM employees E inner join lib_qualification Q
ON E.qualificationCOde = Q.QualificationCode
WHERE deleted = 0
GO 
Select * from vw_EmpQualification
GO

view

Now we are ready to create the INSTEAD OF trigger for insert, delete and update operations on view vw_empQualification.

In the examples I have chosen simple and common scenarios, but there is much that can be done using INSTEAD OF triggers in SQL Server.


INSTEAD OF INSERT Trigger for Insert operation

Our view is comprised of two base tables. If someone tries to insert values using the view the following error will be generated and the insert will fail.

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

At this point, the INSTEAD OF INSERT trigger provides us several options to handle insert operations on this view. For our example we will allow the users to insert data through this view, by having the trigger handle some logic for data integrity.

The INSTEAD OF INSERT trigger will be created using the following script for Insert operations

Script #2: Create INSTEAD OF trigger for handling delete operations on view
USE AdventureWorks
GO
CREATE TRIGGER INSTEADOF_TR_I_EmpQualification 
ON vw_EmpQualification
INSTEAD OF INSERT AS
BEGIN
DECLARE @Code TINYINT
SELECT @Code = qualificationCode 
FROM lib_Qualification L INNER JOIN INSERTED I
ON L.qualification = I.qualification
IF (@code is NULL )
BEGIN
RAISERROR (N'The provided qualification does not exist in qualification library',
16, 1)
RETURN
END
INSERT INTO employees (empcode, name, designation,qualificationCode,deleted) 
SELECT empcode, name, designation, @code, 0 
FROM inserted 
END
GO

We have just used the key word INSTEAD OF versus using the FOR or AFTER keyword in the trigger header and our required INSTEAD OF INSERT trigger has been created. Now we will verify the insert statement on this view which has two base tables. The INSTEAD OF trigger will seamlessly handle the insert operation in the trigger without an error.

Script #3: Insert data in view and verify functionality of INSTEAD OF trigger
USE AdventureWorks
GO 
-- Insert data in view
INSERT INTO vw_EmpQualification VALUES ('425-27-1', 'Emp8','Manager','MBA')
GO
-- To confirm the data insertion
SELECT * FROM vw_EmpQualification
GO

Inserted data is handled in the trigger and our inserted row is shown in the selected data below.

instead of insert


INSTEAD OF UPDATE Trigger for update operation

There may be several scenarios where using INSTEAD of triggers can solve this problem. In the case of views with multiple base tables, you may only issue update statements that affect a single base table at a time. If any update statement on our view affects multiple base tables at a time then the following error would be generated

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

The following script is for an INSTEAD OF UPDATE trigger is used to provided seemless update capability for multiple base tables.

Script #4: Create INSTEAD OF trigger for handling update operations on view
USE AdventureWorks
GO
CREATE TRIGGER INSTEADOF_TR_U_EmpQualification 
ON vw_EmpQualification
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(qualification)) -- If qualification is updated
BEGIN
DECLARE @code TINYINT
UPDATE employees
SET @code = L.qualificationcode 
FROM lib_qualification L INNER JOIN inserted I 
ON L.qualification = I.qualification
IF (@code is NULL )
BEGIN
RAISERROR (N'The provided qualification does not exist in qualification library',
16, 1)
RETURN
END
UPDATE employees
SET qualificationCode = @code
FROM inserted I INNER JOIN employees E ON I.empcode = E.empcode
END

IF (UPDATE(EmpCode)) -- If employee code is updated
BEGIN
RAISERROR (N'You can not edit employee code, Transaction has been failed', 16, 1)
RETURN
END
IF (UPDATE(name)) -- If name is updated 
BEGIN
UPDATE employees
SET name = I.name 
FROM inserted I INNER JOIN employees E ON I.empcode = E.empcode 
WHERE E.empcode = I.empcode
END 

IF (UPDATE(designation)) -- If designation is updated
BEGIN
UPDATE employees
SET designation = I.designation 
FROM inserted I INNER JOIN employees E ON I.empcode = E.empcode 
WHERE E.empcode = I.empcode
END
END
GO

Now we can verify the proper functioning of our trigger for an update statement.

Script #5: Update data in view and verify functionality of INSTEAD OF trigger
USE AdventureWorks
GO 
-- Update data in view
UPDATE vw_EmpQualification
SET designation = 'Designation4 Updated', Qualification = 'MCS'
WHERE empcode = '044-21-3'
GO
-- To confirm the data update
SELECT * FROM vw_EmpQualification
GO

The INSTEAD OF UPDATE trigger handled the update and the update is reflected in the selected data below.

instead of update


INSTEAD OF trigger for delete operation

INSTEAD OF trigger may be attached for delete operations. In our case we are required that when rows are deleted through the view, a deleted flag in the table should be marked "1" for those rows, but rows should not actually be deleted. Such rows may be deleted in bulk later at specified time if needed. For this we may create the following INSTEAD OF DELETE trigger.

Script #6: Create INSTEAD OF trigger for handling delete operations on view
USE AdventureWorks
GO 
CREATE TRIGGER INSTEADOF_TR_D_EmpQualification 
ON vw_EmpQualification
INSTEAD OF DELETE AS
BEGIN
update employees
set deleted = 1
where empcode in (select empcode from deleted)
END
GO

To verify the implementation for deletes the following script can be used

Script #7: Delete data from view and verify functionality of INSTEAD OF trigger
USE AdventureWorks
GO 
-- Delete data in view
DELETE FROM vw_EmpQualification
WHERE Designation = 'Manager'
GO
-- To confirm the data update
SELECT * FROM vw_EmpQualification
SELECT * FROM Employees
GO

The deleted row still exist in the base table, but it is not shown in the view because the deleted flag is set to "1" in the base table.

instead of delete


Summary

After going through these examples for implementing INSTEAD OF triggers for insert, update and delete operations on a view, there are some considerations that are required to keep in mind while planning to implement INSTEAD OF triggers.

  • If you do not specify the INSTEAD OF or AFTER keyword in the trigger header and just use the FOR keyword, then by default an AFTER trigger will be created.
  • You can have only one trigger for each of insert, update or delete option on a single table or view.
  • If you have created an INSTEAD OF trigger on a table then you can not use the cascade option as an UPDATE and DELETE rule. Cascade for delete rule will be prohibited if INSTEAD OF trigger is defined for delete operation and same is the case for update operations.
  • If you have already used cascade for delete or update options for a table then you can not create an INSTEAD OF trigger for that specific DML operation on that table.
  • INSTEAD OF triggers do not work in a recursive manner. Even if you update the same table inside the INSTEAD OF trigger, the trigger will not be invoked a second time. So INSTEAD OF triggers can be used to avoid recursion.
  • You can define both AFTER and INSTEAD OF triggers for the same DML operation on the same table.
  • If defined on an object, an AFTER trigger can be invoked as a result of DML operations from inside the INSTEAD OF trigger.
  • The deleted table for an INSTEAD OF INSERT trigger is always empty and the inserted table for INSTEAD OF DELETE trigger is always empty.

Next Steps

Keeping in mind the basic functionality of INSTEAD OF triggers, these can be used in a number of scenarios. Performance should be considered and properly tested before implementing INSTEAD OF triggers. In some scenarios they can boost the performance e.g. by skipping unnecessary roll backs, but sometimes they may be a threat to performance. Normally using INSTEAD OF triggers for DML operations on views is safe while implementing these for changing the flow of operations. Code implemented inside the INSTEAD OF trigger should be optimized as it performs alternative operations compared to the original requested DML operation.

  • Click here for more information on INSTEAD OF INSERT triggers
  • Click here for more information on INSTEAD OF UPDATE triggers


Last Update: 7/29/2009


About the author
MSSQLTips author Atif Shehzad
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Wednesday, July 29, 2009 - 9:05:42 AM - unclebiguns Read The Tip

I would be concerned because you are using a variable in the insert and update triggers and this makes the triggers inaccurate for set-based inserts or updates.  It's possible that your application may control updates and inserts, but can you guarantee that no one will ever be able to write a set-based insert or update?


Wednesday, July 29, 2009 - 8:37:52 PM - @tif Read The Tip

 I have just communicatd the basic theme of instead of triggers for DML operations. As i mentioned there, there may lot of different scenarios and inside trigger logic depends upon that specific scenario in mind and it can be managed as required or expected.

Regards


Tuesday, August 11, 2009 - 9:06:54 AM - Jeff Moden Read The Tip

[quote user="@tif"]

 I have just communicatd the basic theme of instead of triggers for DML operations. As i mentioned there, there may lot of different scenarios and inside trigger logic depends upon that specific scenario in mind and it can be managed as required or expected[/quote]

Jack is absolutely correct.  Further, the very unfortunate part about that is no where do you explain the grave ramifications of the RBAR code you've written.  Just communicating "the basic theme" will be missed by many developers who read your article and take it as being the correct way to make such triggers.  RBAR must be avoided in triggers at all costs.  In light of the dangerous code present in this article, making excuses about "basic theme" seems a bit irresponsible.


Tuesday, August 25, 2009 - 12:18:40 AM - @tif Read The Tip

 Corbett and Meden, after your comments about this issue, i hope readers of article would take care of internal logic of triggers. For readers , I would like to point two links about avoiding RBAR.

 http://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/

http://www.sqlservercentral.com/articles/T-SQL/61539/

Thanks for your interest and comments

 


Friday, October 23, 2009 - 7:06:45 PM - devrice Read The Tip

I've just read your article and found it very beneficial as I was unaware that triggers could be placed on views.

I do have one question. What is the purpose of placing a self referencing FK on lib_qualification, in which both parent and child use the same column? For the life of me I can't figure out what purpose this serves. I thought it might have the effect of not allowing inserts or deletes but that's not the case.

ALTER TABLE dbo.Lib_Qualification ADD CONSTRAINT FK_Lib_Qualification_Lib_Qualification FOREIGN KEY ( QualificationCode ) REFERENCES dbo.Lib_Qualification( QualificationCode ) ON UPDATE NO ACTION ON DELETE NO ACTION

 Thanks!


Saturday, October 24, 2009 - 12:38:42 AM - @tif Read The Tip

 @devRice: The constraint was meant to be placed between employee table and lib_qualification. In current state it realy serves no purpose. Code would be updated along with removel of RBAR logic. Thanks for your communication.

 Regards 


Friday, February 12, 2010 - 2:10:37 AM - elizas Read The Tip
Sometimes we need to audit all DML operations for tables in a MSSQL database. There are many methods for achieving this, one of the most common approaches is using DML Triggers . But DML triggers can be expensive so we can make use of the OUTPUT clause which is a new TSQL feature available in SQL SERVER 2005 and later. The OUTPUT clause can make use of the INSERTED and DELETED virtual tables just like Triggers. We can use output clause with a table variable , temporary table or a permanent table. Some examples are given below: OUTPUT clause with INSERT statement --------Creating the primary table which will store data CREATE TABLE TestTable (ID INT, FIRSTNAME VARCHAR(100)) ----Declaring a TABLE VARIABLE to store values of OUTPUT clause DECLARE @TmpTable TABLE (ID INT,FIRSTNAME VARCHAR(100)) ----Insert values in real table as well use OUTPUT clause to insert ----values in the table variable. INSERT TestTable (ID, FIRSTNAME) OUTPUT Inserted.ID, Inserted.FIRSTNAME INTO @TmpTable VALUES (1,'FirstVal') -----OUTPUT clause into Table with UPDATE statement --------Creating the primary table which will store data http://www.mindfiresolutions.com/Avoiding-the-use-of-Triggers-in-SQL-Server-165.php

Friday, February 12, 2010 - 4:21:52 AM - @tif Read The Tip

@elizas, thanks for adding further information to this topic. A tip is also posted on this topic. 


Thursday, June 07, 2012 - 12:48:50 AM - Anil Kumar Read The Tip

You made me a follower!!! Thanks a lot for wonderful writing 


Thursday, July 26, 2012 - 2:51:21 PM - khurram cheema Read The Tip

Great article


Monday, November 26, 2012 - 2:26:03 PM - agentTomasz Read The Tip

Your code is very bad because in inserted table can be more than one record. Try e.g.

INSERT INTO vw_EmpQualification VALUES ('425-27-1', 'Emp8','Manager','MBA'),('425-27-2', 'Emp9','X','Y')

Sunday, December 30, 2012 - 10:19:15 PM - CK Read The Tip

I can understand the codes in this tip well. However, I did not realize the potential risk of the RBAR by using the variable approach. After reading the comments from Jack and Jeff, I still do not get it fully. Can anyone elaborate on how the codes in the tip possibly commit the RBAR problem? More importantly, if the codes in the tip are not desirable, what are the set-based alternatives to achieve the same goals as in this tip: Insert/Update/Delete a view based on multiple tables? I would like to see new tip(s) on this issue.

 


Wednesday, March 06, 2013 - 5:23:47 AM - Rajan Mishra Read The Tip

Good article...........................................


Wednesday, May 15, 2013 - 10:05:24 PM - lilel Read The Tip

 

My code is :
CREATE trigger [dbo].[tu_organization]
on [dbo].[organization]
INSTEAD OF UPDATE AS
BEGIN.......

but the system is still pop up:View or function 'organization3' is not updatable because the modification affects multiple base tables.


Thursday, December 12, 2013 - 8:59:53 PM - Aaron Bertrand Read The Tip

You said:

  • You can have only one trigger for each of insert, update or delete option on a single table or view.
That is not correct. Simple proof:

CREATETABLE dbo.f(a INT);

GO

CREATETRIGGER dbo.f1

ON dbo.f FORINSERTASPRINT'f1';

GO

CREATETRIGGER dbo.f2

ON dbo.f FORINSERTASPRINT'f2';

GO

INSERT dbo.f(a)SELECT 1;

 


Thursday, December 12, 2013 - 9:00:36 PM - Aaron Bertrand Read The Tip

(Well, I don't know what happened to the spaces in my comment, but I hope it's clear how to repro that.)


Friday, December 13, 2013 - 9:57:52 AM - Atif Read The Tip

According to context of whole tip i am mentioning about the INSTEAD OF trigger. It can not be more than one per operation on a table. Taking your code with little modification if we try to add two instead of triggers for insert operation on a table then following error will be generated.

Msg 2111, Level 16, State 1, Procedure f2, Line 5
Cannot create trigger 'f2' on table 'dbo.f' because an INSTEAD OF INSERT trigger already exists on this object.

However the statement in the tip should be like

  • You can have only one instead of trigger for each of insert, update or delete option on a single table or view.

Thanks alot for pointing to this confusion.


Friday, December 13, 2013 - 1:19:07 PM - Aaron Bertrand Read The Tip

Yes, I agree, what you say is true for INSTEAD OF triggers, but it is ambiguous if you look at the bullet in isolation - it certainly sounds like you are saying that is true for any type of trigger.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.