如何在T-SQL/SQL Server中为上级/子类型(继承)实体模拟BEFORE INSERT触发器? [英] How to emulate a BEFORE INSERT trigger in T-SQL / SQL Server for super/subtype (Inheritance) entities?
问题描述
这是在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
触发器功能非常不同,在BEFORE
触发器功能中,语句始终是由于执行而引起的,除非您明确地将其回滚.
但是实际上还有另一个问题要解决.正如您的Oracle脚本所揭示的,您需要转换的触发器使用了SQL Server不支持的另一功能,即FOR EACH ROW
. SQL Server中也没有按行触发,只有按语句触发.这意味着您需要始终牢记,插入的数据是行 set ,而不仅仅是一行.这会增加更多的复杂性,尽管这可能会得出您需要说明的事情清单.
那么,这实际上是要解决的两件事:
-
替换
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;
如您所见,在@new_user
中添加了IDENTIY(1,1)
列,因此将从1开始另外枚举临时插入的super_id
值.以提供新的super_id
和新的super_id
之间的映射.数据行,ROW_NUMBER
函数也用于枚举INSERTED
行.结果,INSERTED
集中的每一行现在都可以链接到单个super_id
,从而可以补充要插入到sub1
中的完整数据行.
请注意,新的super_id
的插入顺序可能与它们的分配顺序不匹配.我认为这是没有问题的.生成的所有新的super
行都是相同的,除了ID.因此,您所需要做的只是在每个新的sub1
行中取一个新的super_id
.
但是,如果插入到super
的逻辑更加复杂,并且由于某些原因,您需要准确记住为哪个新的sub
行生成了哪个新的super_id
,您可能需要考虑此堆栈溢出问题中讨论的映射方法:
This is on Azure.
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)
);
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
.
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;
Please advise on how I would simulate this in T-SQL, given that T-SQL lacks the BEFORE INSERT
capability?
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.
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.
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:
replace the
BEFORE
functionality;replace the
FOR EACH ROW
functionality.
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;
This is how the above works:
The same number of rows as being inserted into
sub1
is first added tosuper
. The generatedsuper_id
values are stored in a temporary storage (a table variable called@new_super
).The newly inserted
super_id
s are now inserted intosub1
.
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.
The problem is to assign the new super_id
s 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;
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_id
s 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
.
Note that the order in which the new super_id
s 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.
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:
这篇关于如何在T-SQL/SQL Server中为上级/子类型(继承)实体模拟BEFORE INSERT触发器?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!