我有一个超类型实体和几个子类型实体,后者需要从每个插入的超类型实体的主键获取其外键.在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,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;
解决方法
但请注意,正如这两种触发类型的名称所暗示的那样,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_ids现在插入到sub1中.
没有什么比这更困难了,但只有在sub1中没有其他列而不是你在问题中指定的列时,上述内容才有效.如果还有其他列,则上述触发器需要更复杂一些.
问题是将新的super_ids分别分配给每个插入的行.实现映射的一种方法如下：
CREATE TRIGGER sub_trg
ON sub1
INSTEAD OF INSERT
AS
BEGIN
DECLARE @new_super TABLE (
rownum int IDENTITY (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_ids和新数据行之间的映射,ROW_NUMBER函数用于枚举INSERTED行.因此,INSERTED集合中的每一行现在都可以链接到单个super_id,从而补充到要插入sub1的完整数据行.
请注意,插入新super_ids的顺序可能与它们的分配顺序不匹配.我认为这不是问题.除了ID之外,生成的所有新超级行都是相同的.所以,你需要的只是为每个新的sub1行获取一个新的super_id.
但是,如果插入到super中的逻辑更复杂,并且由于某种原因需要精确记住为哪个新子行生成了哪个新的super_id,那么您可能需要考虑此Stack Overflow问题中讨论的映射方法：
> Using merge..output to get mapping between source.id and target.id
