Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a series of updateable views we are exposing to end users as the interface for a back end process.

One of these views references two tables and requires an INSTEAD OF trigger for UPDATE and INSERTs.

The structure of the tables is (greatly simplified):

Claim
(DataRowID bigint IDENTITY PRIMARY KEY
,<bunch of claim data>)

ClaimExtended
(ClaimDataRowID bigint FOREIGN KEY references dbo.Claim(DataRowID) NOT NULL
,<bunch of other claim data>)

My original plan was to do this in the trigger like so:

CREATE TRIGGER [dbo].[MyTrigger] ON [dbo].[MyView]
INSTEAD OF INSERT
AS

DECLARE @IDLink TABLE
    (RowID int
    ,ClaimDataRowID bigint)

DECLARE @Inserted TABLE
    (RowID int identity (1,1) NOT NULL
    ,<all the columns from the view>)

INSERT INTO
    @Inserted
    (<View columns>)
SELECT
    (<View columns>)
FROM
    Inserted

INSERT INTO
    Claim
    (<Columns>)
OUTPUT
    I.RowID
    ,inserted.ClaimDataRowID
    INTO
        @IDLink (RowID, ClaimDataRowID)
SELECT
    (<Columns>)
FROM
    @Inserted I

INSERT INTO
    ClaimExtended
    (ClaimDataRowID,
    <Columns>)
SELECT
    C.ClaimDataRowID,
    <Columns>
FROM
    @Inserted I
INNER JOIN
    @IDLink C
        ON C.RowID = I.RowID

The OUTPUT clause here is not working, however (Multi-part identifier I.RowID could not be bound) I'm assuming because I can't reference the source table in an INSERT OUTPUT clause.

What other method could I use here besides making the view a table? For other reasons this needs to be a VIEW and the underlying tables are pretty much set in stone.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

Tables:

CREATE TABLE dbo.Claim
(
    DataRowID   bigint IDENTITY NOT NULL,
    ClaimColumn integer NOT NULL,

    CONSTRAINT PK_Claim
        PRIMARY KEY CLUSTERED (DataRowID)
);
GO
CREATE TABLE dbo.ClaimExtended
(
    ClaimDataRowID  bigint NOT NULL,
    ExtendedColumn  integer NOT NULL,

    CONSTRAINT PK_ClaimExtended
        PRIMARY KEY CLUSTERED (ClaimDataRowID),

    CONSTRAINT FK_ClaimExtended_Claim
        FOREIGN KEY (ClaimDataRowID)
        REFERENCES dbo.Claim (DataRowID)
);

View:

CREATE VIEW dbo.MyView
WITH SCHEMABINDING
AS
    SELECT
        c.DataRowID,
        c.ClaimColumn,
        ce.ExtendedColumn
    FROM dbo.Claim AS c
    JOIN dbo.ClaimExtended AS ce ON
        ce.ClaimDataRowID = c.DataRowID;

Instead-of trigger:

CREATE TRIGGER trgMyView_IOI
ON dbo.MyView
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    SET ROWCOUNT 0;

    DECLARE @ExtendedRows AS TABLE
    (
        ClaimDataRowID  bigint PRIMARY KEY, 
        ExtendedColumn  integer NOT NULL
    );

    MERGE dbo.Claim AS c
    USING INSERTED AS ins ON 1 = 0
    WHEN NOT MATCHED THEN 
        INSERT (ClaimColumn)
        VALUES (ins.ClaimColumn)
    OUTPUT
        INSERTED.DataRowID,
        ins.ExtendedColumn
    INTO @ExtendedRows 
        (
            ClaimDataRowID,
            ExtendedColumn
        );

    INSERT dbo.ClaimExtended
        (ClaimDataRowID, ExtendedColumn)
    SELECT
        er.ClaimDataRowID,
        er.ExtendedColumn
    FROM @ExtendedRows AS er;
END;

Example usage:

INSERT dbo.MyView
    (ClaimColumn, ExtendedColumn)
VALUES
    (1000, 2000),
    (1001, 2001);
GO
SELECT
    mv.DataRowID,
    mv.ClaimColumn,
    mv.ExtendedColumn
FROM dbo.MyView AS mv;

Output:

Output

share|improve this answer
    
It compiled, so we will see if it works in test! If it does you shall have a shiny check mark. –  JNK Feb 6 '13 at 17:50
1  
Ooo that check mark is shiny. –  Paul White Feb 8 '13 at 20:54
1  
Moderators get special ones like gold stars in school. –  JNK Feb 11 '13 at 13:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.