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

2022-01-01 00:00:00 azure-sql-database tsql sql-server triggers

这是在 Azure 上.

This is on Azure.

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

I have a supertype entity and several subtype entities, the latter of which needs to obtain their foreign keys from the primary key of the super type entity on each insert. In Oracle, I use a BEFORE INSERT trigger to accomplish this. How would one accomplish this in 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.

I wish for an insert into sub1 to fire a trigger that actually inserts a value into super and uses the super_id generated to put into sub1.

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

In Oracle, this would be accomplished by the following:

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 中模拟这一点?

Please advise on how I would simulate this in T-SQL, given that T-SQL lacks the BEFORE INSERT capability?

推荐答案

有时 BEFORE 触发器可以替换为 AFTER 触发器，但这似乎并不适用在您的情况下就是这种情况，因为您显然需要在插入之前提供一个值.因此，为此目的，最接近的功能似乎是 INSTEAD OF 触发器，如 @marc_s 在他的评论中提出了.

Sometimes a BEFORE trigger can be replaced with an AFTER one, but this doesn't appear to be the case in your situation, for you clearly need to provide a value before the insert takes place. So, for that purpose, the closest functionality would seem to be the INSTEAD OF trigger one, as @marc_s has suggested in his comment.

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

Note, however, that, as the names of these two trigger types suggest, there's a fundamental difference between a BEFORE trigger and an INSTEAD OF one. While in both cases the trigger is executed at the time when the action determined by the statement that's invoked the trigger hasn't taken place, in case of the INSTEAD OF trigger the action is never supposed to take place at all. The real action that you need to be done must be done by the trigger itself. This is very unlike the BEFORE trigger functionality, where the statement is always due to execute, unless, of course, you explicitly roll it back.

但实际上还有一个问题需要解决.正如您的 Oracle 脚本所揭示的，您需要转换的触发器使用了 SQL Server 不支持的另一个特性，即 FOR EACH ROW.SQL Server 中也没有每行触发器，只有每语句触发器.这意味着您需要始终牢记插入的数据是一行set，而不仅仅是一行.这会增加更多的复杂性，尽管这可能会得出您需要考虑的事项清单.

But there's one other issue to address actually. As your Oracle script reveals, the trigger you need to convert uses another feature unsupported by SQL Server, which is that of FOR EACH ROW. There are no per-row triggers in SQL Server either, only per-statement ones. That means that you need to always keep in mind that the inserted data are a row set, not just a single row. That adds more complexity, although that'll probably conclude the list of things you need to account for.

那么，真正要解决两件事:

So, it's really two things to solve then:

  • 替换BEFORE功能；

替换FOR EACH ROW 功能.

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

My attempt at solving these is below:

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;

这是上面的工作原理:

  1. 与插入sub1 相同的行数首先添加到super.生成的 super_id 值存储在临时存储中(一个名为 @new_super 的表变量).

  1. The same number of rows as being inserted into sub1 is first added to super. The generated super_id values are stored in a temporary storage (a table variable called @new_super).

新插入的super_id现在被插入到sub1中.

The newly inserted super_ids are now inserted into sub1.

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

Nothing too difficult really, but the above will only work if you have no other columns in sub1 than those you've specified in your question. If there are other columns, the above trigger will need to be a bit more complex.

问题是将新的 super_id 分别分配给每个插入的行.实现映射的一种方法可能如下所示:

The problem is to assign the new super_ids to every inserted row individually. One way to implement the mapping could be like below:

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;

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

As you can see, an IDENTIY(1,1) column is added to @new_user, so the temporarily inserted super_id values will additionally be enumerated starting from 1. To provide the mapping between the new super_ids and the new data rows, the ROW_NUMBER function is used to enumerate the INSERTED rows as well. As a result, every row in the INSERTED set can now be linked to a single super_id and thus complemented to a full data row to be inserted into sub1.

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

Note that the order in which the new super_ids are inserted may not match the order in which they are assigned. I considered that a no-issue. All the new super rows generated are identical save for the IDs. So, all you need here is just to take one new super_id per new sub1 row.

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

If, however, the logic of inserting into super is more complex and for some reason you need to remember precisely which new super_id has been generated for which new sub row, you'll probably want to consider the mapping method discussed in this Stack Overflow question:

  • 使用merge..output获取 source.id 和 target.id 之间的映射

相关文章