How to write an INSTEAD OF INSERT trigger on a multi table view that works with identities?

Question

I have two tables that have a 1:1 relationship and use IDENTITY columns for their primary keys.

A view is joining both tables together to give the impression that all columns are really stored in one table.

For viewing purposes this is fine, but the view needs an INSTEAD OF INSERT trigger as well.

How do you write such a trigger that splits the column values across both tables?

Note: There is no candidate key other than the identity column in the parent table. Otherwise this question may help:

INSERT INTO View, INSTEAD OF Trigger, Identity, multiple tables?

(There is also a possible solution that declares a variable for every field in an answer of that question.)

Simplified schema (I left out PKEYs, FKEYs, COLLATIONS, etc.):

CREATE TABLE
  [dbo].[parent]
(
    [parent_id] INT IDENTITY(1,1)
  , [name]      NVARCHAR(100)
)

CREATE TABLE
  [dbo].[child]
(
    [parent_id] INT NOT NULL
  , [child_id]  INT IDENTITY(1,1)
  , [name]      NVARCHAR(100)
)

GO

CREATE VIEW
  [dbo].[parent_child]
AS
SELECT
    par.[parent_id]
  , par.[name]      AS "parent_name"
  , chi.[child_id]
  , chi.[name]      AS "child_name"
FROM
  [dbo].[parent] par
LEFT OUTER JOIN
  [dbo].[child] chi
ON
  chi.[parent_id] = par.[parent_id]

GO

The trigger template:

CREATE TRIGGER
  [dbo].[parent_child_instead_of_insert]
ON
  [dbo].[parent_child]
INSTEAD OF INSERT
AS
BEGIN
  -- Implementation here
END
GO

Example data:

INSERT INTO 
  [dbo].[parent_child]
(
    [parent_name]
  , [child_name]
)
SELECT 
    'parent1'
  , 'child1' 
UNION 
SELECT 
    'parent2'
  , 'child2'

score 1 · Accepted Answer · edited May 23 '17 at 12:18

Inspired by the question Future-proofing an INSTEAD OF INSERT trigger I came up with a solution which uses a temporary table #inserted that is a complete copy of the inserted table.

The trigger adds a temporary identity column on that table to make it possible to iterate over the inserted rows using a unique value.

The rest is then a simple loop using a cursor that inserts each row into the parent table first and uses the SCOPE_IDENTITY() to insert the child row.

This solution has the advantage over the "declare a var for every column" solution that you have to fetch only the temporary identity value instead of all columns.

Performance wise it is probably not very good, because all data in the inserted table must be copied.

SELECT * INTO [dbo].[#inserted] FROM [inserted]
ALTER TABLE [dbo].[#inserted] ADD [temp_id] INT IDENTITY(1,1)

DECLARE
  @temp_id int

DECLARE
  cur CURSOR
FOR
SELECT
  [temp_id]
FROM 
  [dbo].[#inserted]

OPEN cur
FETCH cur INTO @temp_id
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO 
  [dbo].[parent]
(
  [name]
)
SELECT
  [parent_name]
FROM
  [#inserted]
WHERE
  [temp_id] = @temp_id

INSERT INTO
  child
(
    [parent_id]
  , [name]
)
SELECT
    SCOPE_IDENTITY()
  , [child_name]
FROM
  [dbo].[#inserted]
WHERE
  [temp_id] = @temp_id

FETCH cur INTO @temp_id
END

CLOSE cur
DEALLOCATE cur

score 0 · Answer 2 · edited May 23 '17 at 10:33

You can iterate over the inserted table by declaring a variable for each column.

DECLARE
  @parent_name NVARCHAR(100)

DECLARE
  @child_name NVARCHAR(100)

DECLARE
  cur CURSOR
FOR
SELECT
    [parent_name]
  , [child_name]
FROM 
  [inserted]

OPEN cur
FETCH cur INTO @parent_name, @child_name
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO 
  [dbo].[parent]
(
  [name]
)
SELECT
  @parent_name

INSERT INTO
  child
(
    [parent_id]
  , [name]
)
SELECT
    SCOPE_IDENTITY()
  , @child_name

FETCH cur INTO @parent_name, @child_name
END

CLOSE cur
DEALLOCATE cur

I got the idea from this answer: https://stackoverflow.com/a/15162860/426242

How to write an INSTEAD OF INSERT trigger on a multi table view that works with identities?

2 Answers2

Linked