patternsqlMinor
Getting Identity values to use as FK in an INSTEAD OF trigger
Viewed 0 times
identitytriggergettinginsteadvaluesuse
Problem
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
The structure of the tables is (greatly simplified):
My original plan was to do this in the trigger like so:
The
What other method could I use here besides making the view a table? For other reasons this needs to be a
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
,)
ClaimExtended
(ClaimDataRowID bigint FOREIGN KEY references dbo.Claim(DataRowID) NOT NULL
,)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
,)
INSERT INTO
@Inserted
()
SELECT
()
FROM
Inserted
INSERT INTO
Claim
()
OUTPUT
I.RowID
,inserted.ClaimDataRowID
INTO
@IDLink (RowID, ClaimDataRowID)
SELECT
()
FROM
@Inserted I
INSERT INTO
ClaimExtended
(ClaimDataRowID,
)
SELECT
C.ClaimDataRowID,
FROM
@Inserted I
INNER JOIN
@IDLink C
ON C.RowID = I.RowIDThe
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.Solution
Tables:
View:
Instead-of trigger:
Example usage:
Output:
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:
Code Snippets
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)
);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;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;INSERT dbo.MyView
(ClaimColumn, ExtendedColumn)
VALUES
(1000, 2000),
(1001, 2001);
GO
SELECT
mv.DataRowID,
mv.ClaimColumn,
mv.ExtendedColumn
FROM dbo.MyView AS mv;Context
StackExchange Database Administrators Q#34258, answer score: 8
Revisions (0)
No revisions yet.