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.
Explanation
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 an 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 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
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning. He studied at Universidad de Buenos Aires. Daniel started working as a programmer at a young age. Over the years he specialized in databases, particularly SQL Server and Oracle. Now with 30 years of age, his work experience includes working with various technologies like VB, C, .NET, web development, Windows and Linux systems. He likes to read about science, psychology, philosophy and many other things. In his spare time, he trains powerlifting aiming to compete.
- MSSQLTips Awards: Author of the Year – 2018 | Champion (100+ tips) – 2018 | Author Contender – 2015-2017, 2019
I have tables with dozens of columns. Dynamic SQL doesn’t work on the Inserted or Deleted columns directly and materializing the tables as temp tables is horribly expensive if you’re only updating, say, 4 columns out of 100.
What I’m looking for is something similar to Oracle’s true BEFORE triggers to do things like update Modified_On and Modified_By columns without having to make a separate “after” update.
Also, I might just be missing it but I’m not seeing any “First Published” dates on any of these articles. The only clue as to when they’ve been written is when someone leaves a comment, which is dated. This is a general site problem and not an author problem.
I’ve searched everywhere and no one seems to have a good method for handling such wide table using INSTEAD OF triggers. Does anyone have a link to such an article?