Zhyke Zhyke - 21 days ago 9
C# Question

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.

Answer Source

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.