如何在使用身份的多表视图上编写INSTEAD OF INSERT触发器？ [英] How to write an INSTEAD OF INSERT trigger on a multi table view that works with identities?

问题描述

我有两个具有1：1关系的表，并使用 IDENTITY 列作为其主键。

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.

出于查看目的，这很好，但是视图需要 INSTEAD OF INSERT 触发器。

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?

注意：除父表中的identity列外，没有其他候选键。否则，这个问题可能会有所帮助：

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

插入视图，插入触发器，身份，多个表？

（还有一种可能的方法是声明一个变量

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

简化的模式（我省略了PKEY，FKEY，COLLATIONS等）：

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

触发模板：

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

示例数据：

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

推荐答案

受到以下问题的启发：对INSTEAD OF INSERT触发器进行未来的保护我想出了一个使用临时表 #inserted 的解决方案插入的表的完整副本。

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.

然后剩下的就是一个简单的循环，使用游标，该游标首先将每一行插入父表并使用 SCOPE_IDENTITY（）插入子行。

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.

此解决方案比为每个列声明var具有优势

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

这篇关于如何在使用身份的多表视图上编写INSTEAD OF INSERT触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！