关于自引用键的更新级联-SQL Server

2022-03-08 00:00:00 sql-server triggers cascade ssms
我正在尝试找出如何使用触发器在自引用时态表上执行UPDATE CASCADE。虽然我发现这个帖子(On delete cascade for self-referencing table)最接近我的答案,但我有以下问题:

帖子中的答案和问题似乎不完整。您能告诉我帖子里Deleted表里有什么吗?Deleted表和Comments表中的id是什么?它是主键吗?如果主键是一对列怎么办?此外,我也不确定为什么ID包含在CTE ID中。似乎是不正确的。我不确定。

如果表具有其他外键约束,则特定于UPDATE级联的触发器将是什么样子?

我有下面的表格设置。您能帮我在外键FK_SON_HEIGHT_WEIGHT上创建UPDATE CASCADE的触发器吗? 我可以在这里使用代理键,但是有几个表具有对PK_HEIGHT_WEIGHT的外键引用。是否有办法确保我不需要代理键?

(注意:出于隐私原因,该表已被修改)

创建表号Man(

Height varchar(100) NOT NULL,
Weight varchar(50) NOT NULL,
CONSTRAINT PK_Height_Weight PRIMARY KEY (Height, Weight),
CONSTRAINT FK_Weight FOREIGN KEY (Weight)
REFERENCES no.Human (Weight)
On Update Cascade,
Son_Height varchar (100) NOT NULL, 
Son_Weight varchar (50) NOT NULL, 
CONSTRAINT FK_Son_Height_Weight FOREIGN KEY (Son_Height, Son_Weight) 
REFERENCES no.Man(Height,Weight)
On Update Cascade
)

解决方案

由于该表是一个时态表,因此只能对其使用"更新后"触发器,而不能使用"INSTEAD OF UPDATE"触发器(link以供参考)。我现在使用下面的解决方案将内容设置为空。这并不是一个完整的答案-但我正在努力与社区分享我所知道的。

CREATE or ALTER TRIGGER no.Weight_Height
ON no.Man
After UPDATE
AS
Set nocount on
IF ( UPDATE (Height) AND UPDATE (Weight) )
    ;With BA_DEL_Join as
    (
    select d.Height as d_Height, d.Weight as d_Weight, 
        d.Son_Height_Number as d_Son_Height_Num, d.Son_Weight as 
        d_Son_Weight, ba.Height as ba_Height, ba.Weight as ba_Weight, 
        ba.Son_Height_Number as ba_Son_Height_Num, ba.Son_Weight as 
        ba_Son_Weight 
    from deleted d
    inner join no.Man ba
    on d.Height = ba.Son_Height_Number and d.Weight = ba.Son_Weight
    )

    Update no.Man
    set Son_Height_Number = NULL, Son_Weight = NULL
    from BA_DEL_Join
    where d_Height = ba_Son_Height_Num and d_Weight = ba_Son_Weight
    Go

即使此代码模拟级联事件,它也不会影响引用完整性。

相关文章