SQL Server Trigger Instead of Update

By: Daniel Farina

Overview

In this chapter of the tutorial, I will show you how we can create an instead of update trigger for a View that references two tables.

Sample Test Scenario

We are continuing with the scenario of the previous chapter, which consisted of two tables named Customers and Providers and a view named Person that serves as a wrapper to access both tables.

The logic for this instead of update trigger is very simple. It consists of two update queries. The first is an update to the customers table and the second is another update but this time to the Providers table. In both queries we join against the inserted pseudo table in order to obtain the modified rows.

CREATE TRIGGER TR_U_Person
ON dbo.Person
INSTEAD OF UPDATE
AS
UPDATE dbo.Customers
SET    CustomerName = I.PersonName , CustomerAddress = I.PersonAddress
FROM   dbo.Customers C
       INNER JOIN Inserted I ON C.CustomerCode = I.PersonCode;
 
UPDATE dbo.Providers
SET    ProviderName = I.PersonName , ProviderAddress = I.PersonAddress
FROM   dbo.Providers P
       INNER JOIN Inserted I ON P.ProviderCode = I.PersonCode;
GO

Let's test this trigger by running the following updates.

SELECT * FROM dbo.Person;
GO

UPDATE Person SET PersonName = 'Jennifer Diaz' WHERE PersonCode = 'C2'
UPDATE Person SET PersonName = 'Christian Gomes' WHERE PersonCode = 'P2'
GO

SELECT * FROM dbo.Person;

In the next screen capture you can see the results of executing the code.

Additional Information

• How to find Updated Column in SQL Server Trigger
• SQL Server Trigger on View Example