SQL Server Trigger Instead of Insert


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

Instead of Insert trigger Inserting rows in the Person view.
Additional Information

Last Update: 8/2/2021




Comments For This Article





download














get free sql tips
agree to terms