By: Daniel Farina
Overview
In this chapter we will see with a practical example how an instead of insert trigger works and how it differs with after insert triggers.
Sample Test Scenario
Suppose we have two tables named Customers and Providers that store information of customers and providers respectively. Both tables are not accessed directly but instead there is a view named Person that uses both entities.
The Customers and Providers tables have a primary key with an identity column, a computed column that adds a "C" or "P" to the beginning of the identity value, name and address. Take a look at the next code section to see the creation script for both tables.
CREATE TABLE Customers (
CustomerId INT IDENTITY(1,1),
CustomerCode AS 'C' + CAST(CustomerId AS VARCHAR(10) ),
CustomerName VARCHAR(100),
CustomerAddress VARCHAR(100),
)
GO
CREATE TABLE Providers (
ProviderId INT IDENTITY(1,1),
ProviderCode AS 'P' + CAST(ProviderId AS VARCHAR(10) ),
ProviderName VARCHAR(100),
ProviderAddress VARCHAR(100),
)
GO
INSERT INTO dbo.Customers (CustomerName, CustomerAddress) VALUES ('James Doe', '134 Evergreen Av.')
INSERT INTO dbo.Providers (ProviderName, ProviderAddress) VALUES ('Martin King', '14th street 421.')
GO
Now let's create the Persons view. As you can see in the next script, the view adds a column named Type that has a fixed value of Customer or Provider depending on which table we are querying.
CREATE VIEW Person
AS
SELECT CustomerCode AS PersonCode ,
CustomerName AS PersonName ,
CustomerAddress AS PersonAddress,
'Customer' AS [Type]
FROM dbo.Customers
UNION ALL
SELECT ProviderCode AS PersonCode ,
ProviderName AS PersonName ,
ProviderAddress AS PersonAddress,
'Provider' AS [Type]
FROM dbo.Providers;
GO
Coding an Instead of Insert trigger for this view is very straightforward and you will see it is similar to the view creation script. We can think of this trigger as two part code segments where in the first we deal with the insertion of Customers by filtering the Inserted pseudo table for the rows of type "Customer", and then we do the same with the Providers but this time filtering the rows of type "Provider".
CREATE TRIGGER TR_I_Person ON dbo.Person INSTEAD OF INSERT AS INSERT INTO dbo.Customers ( CustomerName , CustomerAddress ) SELECT I.PersonName , I.PersonAddress FROM Inserted I WHERE I.Type = 'Customer' INSERT INTO dbo.Providers ( ProviderName , ProviderAddress ) SELECT I.PersonName , I.PersonAddress FROM Inserted I WHERE I.Type = 'Provider' GO
In order to test the trigger, the next script inserts one customer and one provider to the person table and prints the output to the screen.
SELECT * FROM Customers; SELECT * FROM Providers; SELECT * FROM Person; INSERT INTO dbo.Person ( PersonName , PersonAddress , [Type] ) VALUES ( 'Christian Gomez', '12th Street 125', 'Provider' ); INSERT INTO dbo.Person ( PersonName , PersonAddress , [Type] ) VALUES ( 'Jenny Diaz', 'Riverside 123', 'Customer' ); SELECT * FROM Person;
The next screen capture shows the execution of the script.
