SQL Server - Using a VIEW to update multiple base tables

Question

I have a "base" table that I am looking to extend with "custom" fields - but I don't want to alter the integrity of the base table (by simply adding my custom columns to it). Here are the table definitions:

CREATE TABLE CHRIS_BASE (
    ID      UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    B1      VARCHAR(20),
    B2      VARCHAR(20)
    PRIMARY KEY(ID)
)


CREATE TABLE CHRIS_CUSTOM (
    ID          UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES dbo.CHRIS_BASE(ID) ON DELETE CASCADE,
    C1          VARCHAR(20),
    C2          VARCHAR(20)
    PRIMARY KEY (ID) 
)

To get a singular view of all the relevant data, I have created a view:

CREATE VIEW CHRIS_VW AS
    SELECT cb.ID, cb.B1, cb.B2, cc.C1, cc.C2
    FROM dbo.CHRIS_BASE AS cb
    LEFT OUTER JOIN dbo.CHRIS_CUSTOM AS cc ON cc.ID = cb.ID

How would I create the "INSTEAD OF" triggers to handle this? The most important thing is obviously that the keys match when I perform an insert. I tried this:

CREATE TRIGGER CHRIS_VW_TR_INSERT ON CHRIS_VW
INSTEAD OF INSERT
AS
BEGIN

    INSERT INTO dbo.CHRIS_BASE (B1, B2)
    SELECT B1, B2
    FROM INSERTED

    INSERT INTO dbo.CHRIS_CUSTOM (C1, C2)
    SELECT C1, C2
    FROM INSERTED

END

But I am obviously getting an error - because I cannot carry the generated ID from the "base" table to the "custom" table.

INSERT INTO CHRIS_VW
    (B1, B2, C1, C2)
VALUES
    ('R1-F1-BASE','R1-F2-BASE','R1-F1-CUSTOM','R1-F2-CUSTOM'),
    ('R2-F1-BASE','R2-F2-BASE','R2-F1-CUSTOM','R2-F2-CUSTOM'),
    ('R3-F1-BASE','R3-F2-BASE','R3-F1-CUSTOM','R3-F2-CUSTOM'),
    ('R4-F1-BASE','R4-F2-BASE','R4-F1-CUSTOM','R4-F2-CUSTOM')

Yields:

(4 rows affected)
Msg 515, Level 16, State 2, Procedure CHRIS_VW_TR_INSERT, Line 10 [Batch Start Line 43]
Cannot insert the value NULL into column 'ID', table 'AAD.dbo.CHRIS_CUSTOM'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Also looking for direction on the UPDATE and DELETE triggers as well.

user_1144035 · Answer

Use the OUTPUT clause, but it gets complicated. If we assume that B1/B2 are unique when the data is loaded:

BEGIN
    DECLARE @ids TABLE (@id UNIQUEIDENTIFIER);

    INSERT INTO dbo.CHRIS_BASE (B1, B2)
        SELECT B1, B2
        FROM INSERTED
        OUTPUT id INTO @ids;

    INSERT INTO dbo.CHRIS_CUSTOM (ID, C1, C2)
        SELECT ids.id, i.C1, i.C2
        FROM INSERTED i JOIN
             @ids ids
             ON i.B1 = ids.B1 AND i.B2 = ids.B2;
END;

SQL Server - Using a VIEW to update multiple base tables

1 Answers