如何在 T-SQL / SQL Server 中为超/子类型(继承)实体模拟插入前触发器

这是在Azure上。

我有一个超类型实体

和几个子类型实体，后者需要从每次插入的超类型实体的主键中获取它们的外键。在 Oracle 中，我使用 BEFORE INSERT 触发器来完成此操作。如何在SQL Server/T-SQL中实现这一点？

DDL

CREATE TABLE super (
 super_id int IDENTITY(1,1)
 ,subtype_discriminator char(4) CHECK (subtype_discriminator IN ('SUB1', 'SUB2')
 ,CONSTRAINT super_id_pk PRIMARY KEY (super_id)
);
CREATE TABLE sub1 (
 sub_id int IDENTITY(1,1)
,super_id int NOT NULL
,CONSTRAINT sub_id_pk PRIMARY KEY (sub_id)
,CONSTRAINT sub_super_id_fk FOREIGN KEY (super_id) REFERENCES super (super_id)
);

我希望插入sub1以触发一个触发器，该触发器实际上将值插入super并使用生成的super_id放入sub1。

在 Oracle 中，这将通过以下方式完成：

CREATE TRIGGER sub_trg
    BEFORE INSERT ON sub1
    FOR EACH ROW
DECLARE
    v_super_id int; //Ignore the fact that I could have used super_id_seq.CURRVAL
BEGIN
    INSERT INTO super (super_id, subtype_discriminator) 
        VALUES (super_id_seq.NEXTVAL, 'SUB1') 
        RETURNING super_id INTO v_super_id;
    :NEW.super_id := v_super_id;
END;

鉴于 T-SQL 缺乏BEFORE INSERT功能，请告知我将如何在 T-SQL 中模拟它？

有时可以将

BEFORE触发器替换为AFTER触发器，但在您的情况下似乎并非如此，因为您显然需要在插入之前提供一个值。因此，为此目的，最接近的功能似乎是INSTEAD OF触发功能，正如@marc_s在他的评论中所建议的那样。

但请注意，正如这两种触发器类型的名称所暗示的那样，BEFORE触发器和INSTEAD OF触发器之间存在根本区别。虽然在这两种情况下，触发器都是在调用触发器的语句确定的操作尚未发生时执行的，但在INSTEAD OF触发器的情况下，根本不应该发生操作。您需要完成的实际操作必须由触发器本身完成。这与BEFORE触发器功能非常不同，在触发器功能中，语句总是要执行，当然，除非您显式回滚它。

但实际上还有一个问题需要解决。正如您的 Oracle 脚本所揭示的那样，您需要转换的触发器使用 SQL Server 不支持的另一个功能，即 FOR EACH ROW 。SQL Server 中也没有每行触发器，只有每行触发器。这意味着您需要始终记住，插入的数据是一组行，而不仅仅是一行。这增加了更多的复杂性，尽管这可能会结束您需要考虑的事情列表。

所以，这实际上是要解决的两件事：

替换BEFORE功能;
替换FOR EACH ROW功能。

我解决这些问题的尝试如下：

CREATE TRIGGER sub_trg
ON sub1
INSTEAD OF INSERT
AS
BEGIN
  DECLARE @new_super TABLE (
    super_id int
  );
  INSERT INTO super (subtype_discriminator)
  OUTPUT INSERTED.super_id INTO @new_super (super_id)
  SELECT 'SUB1' FROM INSERTED;
  INSERT INTO sub (super_id)
  SELECT super_id FROM @new_super;
END;

这是上述工作原理：

将插入sub1的行数相同的行数添加到super 中。生成的super_id值存储在临时存储(称为 @new_super 的表变量(中。
新插入的super_id现在已插入到sub1 中。

实际上没什么太难的，但是只有当您sub1在问题中指定的列之外没有其他列时，上述内容才有效。如果还有其他列，上面的触发器需要稍微复杂一些。

问题是将新super_id分别分配给每个插入的行。实现映射的一种方法如下：

CREATE TRIGGER sub_trg
ON sub1
INSTEAD OF INSERT
AS
BEGIN
  DECLARE @new_super TABLE (
    rownum   int IDENTITY (1, 1),
    super_id int
  );
  INSERT INTO super (subtype_discriminator)
  OUTPUT INSERTED.super_id INTO @new_super (super_id)
  SELECT 'SUB1' FROM INSERTED;
  WITH enumerated AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rownum
    FROM inserted
  )
  INSERT INTO sub1 (super_id, other columns)
  SELECT n.super_id, i.other columns
  FROM enumerated AS i
  INNER JOIN @new_super AS n
  ON i.rownum = n.rownum;
END;

如您所见，IDENTIY(1,1)列被添加到 @new_user 中，因此临时插入的super_id值将从 1 开始额外枚举。为了提供新super_id和新数据行之间的映射，ROW_NUMBER 函数还用于枚举INSERTED行。因此，INSERTED集中的每一行现在可以链接到单个super_id，从而补充到要插入sub1的完整数据行。

请注意，插入新super_id的顺序可能与分配它们的顺序不匹配。我认为这不是问题。除 ID 外，生成的所有新super行都是相同的。因此，您在这里所需要的只是为每个新sub1行获取一个新super_id。

但是，如果插入super的逻辑更复杂，并且由于某种原因，您需要准确记住为哪个新sub行生成了哪些新super_id，则可能需要考虑此堆栈溢出问题中讨论的映射方法：

使用合并..用于获取 source.id 和 target.id 之间的映射的输出

虽然 Andriy 的建议适用于少量记录的插入，但由于"枚举"和"@new_super"都没有索引，因此将在最终连接上完成全表扫描，从而导致大型插入的性能不佳。

这可以通过在@new_super表上指定主键来解决，如下所示：

DECLARE @new_super TABLE (
  row_num INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  super_id   int
);

这将导致 SQL 优化器扫描"枚举"表，但在@new_super上执行索引联接以获取新键。

相关内容

最新更新

热门标签：