我有一个超类型实体和几个子类型实体,后者需要从每个插入的超类型实体的主键获取它们的外键.在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_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如何在T-sql中进行模拟,因为T-sql缺少BEFORE INSERT功能？
谢谢
解决方法
但是请注意,正如这两种触发类型的名称所示,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_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_id,您可能需要考虑此堆栈溢出问题中讨论的映射方法：
> Using merge..output to get mapping between source.id and target.id
