如何在使用身份的多表视图上编写INSTEAD OF INSERT触发器？

admin

如何在使用身份的多表视图上编写INSTEAD OF INSERT触发器？

sql

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

视图将两个表连接在一起，给人的印象是所有列实际上都存储在一个表中。

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

您如何编写这样的触发器来在两个表之间拆分列值？

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

简化的架构（我省略了PKEY，FKEY，COLLATIONS等）：

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'

阅读 206

2021-07-01

共1个答案

admin

受这个问题的启发，我对一个INSTEAD OF
INSERT触发器进行了以后的检验，提出了一个使用临时表的解决方案，该临时表#inserted是该inserted表的完整副本。

触发器在该表上添加了一个临时标识列，从而可以使用唯一值遍历插入的行。

剩下的就是使用游标的简单循环，该游标首先将每一行插入到父表中，然后使用SCOPE_IDENTITY()插入子行。

与“为每列声明一个变量”解决方案相比，此解决方案具有优势，即您只需要获取临时标识值而不是所有列即可。

在性能方面，它可能不是很好，因为必须复制插入表中的所有数据。

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

2021-07-01