CODEWITHSUNDEEP
c#sql-servertriggers
Zhyke
Zhyke

Reputation: 447

Set value of a column in INSTEAD OF INSERT

I have two forms namely frmSupplier and frmCustomer that allows user to add and update Customer and Supplier information. Currently we have a two table in a SQL Server database for Customer and Supplier. We are migrating the two tables into one called Contact and converted the Customer and Supplier table to a view. We did this because we want that the code in our application will no longer be modified since this will take time.

I do researched and found out INSTEAD OF INSERT triggers.

This is the SQL:

CREATE TABLE [dbo].[Contact]
(
    [ContactID] [int] IDENTITY(1,1) NOT NULL,
    [Code] [varchar](20) NULL,  
    [ContactName] [varchar](52) NULL,
    [Active] [bit] NULL,
    [Customer] [bit] NULL,
    [Supplier] [bit] NULL,
    [DeliveryAddr1] [varchar](255) NULL,
    [DeliveryAddr2] [varchar](75) NULL,
    [DeliveryAddr3] [varchar](75) NULL,
    [DeliveryAddr4] [varchar](75) NULL, 
    [CreateDate] [datetime] NULL,
    [LastUpdated] [datetime] NULL,
    [TempID] [int] NULL,

    CONSTRAINT [PK_Contact] 
        PRIMARY KEY CLUSTERED ([ContactID] ASC)
) ON [PRIMARY]
GO

Creating view for customers:

CREATE VIEW [dbo].[Customer]
AS
    SELECT        
        ContactID AS CustID, Code AS CustCode, 
        ContactName AS CustName, Active, Customer, 
        DeliveryAddr1, DeliveryAddr2, DeliveryAddr3, DeliveryAddr4, 
        CreateDate, LastUpdated
    FROM            
        dbo.Contact
    WHERE        
        (Customer = 1)
GO

View for suppliers:

CREATE VIEW [dbo].[Supplier]
AS
    SELECT        
        ContactID AS SuppID, Code AS SuppCode, 
        ContactName AS SuppName, Active, 
        DeliveryAddr1, DeliveryAddr2, DeliveryAddr3, DeliveryAddr4, 
        CreateDate, LastUpdated
    FROM            
        dbo.Contact
    WHERE        
        (Supplier = 1)

I want that when a supplier is inserted, it will insert it to Contact and set Supplier=1 or if a customer is inserted, it will set Customer=1.

This is my trigger:

CREATE TRIGGER trgNewCust 
ON dbo.Contact
INSTEAD OF INSERT
AS
BEGIN  
    INSERT INTO Customer
        SELECT 
            [CustCode], [CustName], [Active],
            [DeliveryAddr1], [DeliveryAddr2], [DeliveryAddr3], [DeliveryAddr4]
        FROM 
            inserted
END

Where will I set the Customer=1 or Supplier=1? Another question is how will I know that the newly inserted item in the Contact is for supplier or customer?

Values are inserting in a C# application using INSERT INTO Customer... for customer and INSERT INTO Supplier... for supplier.

Upvotes: 2

Views: 65

Answers (1)

marc_s
marc_s

Reputation: 754468

You need to have your INSTEAD OF INSERT triggers on the VIEWS - not the Contact table! 

CREATE TRIGGER trgNewCust 
ON dbo.Customer      -- trigger must be on the VIEW - not the underlying table!
INSTEAD OF INSERT
AS
BEGIN  
    -- *ALWAYS* explicitly define the list of columns you're inserting into!
    INSERT INTO Contact(Code, ContactName, Active, Customer, Supplier,
                        DeliveryAddr1, DeliveryAddr2, DeliveryAddr3, DeliveryAddr4)
        SELECT 
            CustCode, CustName, Active, 1, 0,
            DeliveryAddr1, DeliveryAddr2, DeliveryAddr3, DeliveryAddr4
        FROM 
            inserted
END

So here, in your trigger on the Customer view, you set Customer=1, Supplier=0 - you apply the same trigger logic to the Supplier view and insert the data into the Contact table, setting Customer=0, Supplier=1

Now, from your code, if you "insert" something into the Customers view, a row in the dbo.Contact table will be created, and the same happens when you "insert" into the Supplier view.

Upvotes: 2

Related Questions