SQL Server Trigger Instead of Update


By:
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.

Instead of Update trigger Updating  rows in the Person view.
Additional Information





Comments For This Article




Wednesday, August 14, 2024 - 10:54:59 AM - Jeff Moden Back To Top (92450)
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?














get free sql tips
agree to terms