SQL Server Trigger Instead of Update
By: Daniel Farina
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.