级联更新/删除在SQL Server内部如何工作？ [英] How cascade Update/Delete works internally in SQL Server?
问题描述
好的，我相信这个问题还不清楚。
Ok, I believe the question was not clear. Here i rewrite this in other way.
假设我创建了两个表，
-
table1（c1 int主键）
table2（ table1c11 int）
table1 和 table2
，即 table1.c1 = table2.table1c11
然后，我在 table1 和 table2 <中执行以下语句/ p>
And, i execute the following statement in the table1 and table2
insert into table1(c1)
values('a'),('b'),('c'),('d'),('e')
insert into table2(table1c11)
values('a'),('a'),('b'),('d')
现在我要实现的是，一旦更新了 table1 中的 c1 自动更改 table2 中的相应数据。为此，我需要在 table1 和 table2 关系中创建约束，并应用 CASCADE UPDATE 。
And now what I want to achieve is that, once I update the value of c1 in table1 the corresponding data in table2 gets changed automatically. For this I need to create the constraint in table1 and table2 relationships and apply the CASCADE UPDATE.
因此，稍后我在 table1 中应用新的SQL更新语句，即
So, later I apply a new SQL update statement in table1 i.e.
Update table1 set c1=c1+'updated'
然后， table2 中的数据也被更改，但是如果我想通过 INSTEAD OF UPDATE TRIGGER ，那么我需要编写而不是更新触发器，在其中，我需要使用两个魔术表 INSERTED 和 DELETED 。
Then the data in table2 gets changed also, But what if I want to achieve the same functionality via INSTEAD OF UPDATE TRIGGER, then I need to write the instead of update trigger and inside that, I need to handle that with two magic tables INSERTED and DELETED.
但要点是，在这种情况下，表1中仅存在一列，我正在更新同一列，所以我该如何映射插入和删除的行。如果我使用CASCADing，SQL Server也将完成同样的事情。
But the main point is that, in this case, I have only one column present in the table1 and I am updating that same column, so how could i map the inserted and deleted rows. Same thing is being done by the SQL Server as well if I use CASCADing.
因此，问题出在表中主键数据发生更改的情况下，SQL Server如何处理批处理更新。
So, the question arises how SQL Server handles batch update in case of the primary key data changes in the table.
推荐答案
因此，问题出在
表中主键数据更改的情况下，SQL Server如何处理批量更新。
So, the question arises how SQL Server handles batch update in case of the primary key data changes in the table.
SQL Server为更新两个表的更新语句建立查询计划。
SQL Server builds a query plan for the update statement that update both tables.
创建表：
create table T1
(
T1ID int primary key
);
create table T2
(
T2ID int primary key,
T1ID int references T1(T1ID) on update cascade
)
添加一些数据：
insert into T1 values(1), (2)
insert into T2 values(1, 1), (2, 1), (3, 2)
更新 T1 的主键：
update T1
set T1.T1ID = 3
where T1.T1ID = 1
更新查询计划如下：
该计划有两个聚集索引更新步骤，一个用于 T1 和一个 T2 。
The plan has two Clustered Index Update steps, one for T1 and one for T2.
更新1：
当更新多个主键值时，SQL Server如何跟踪要更新的行？
How does SQL Server keep track of the rows to update when more than one primary key value is updated?
update T1
set T1.T1ID = T1.T1ID + 100
Easer假脱机在顶部分支（ T1 的更新）中保存了旧的 T1ID 和新计算出的 T1ID（Expr1013）到下级分支使用的临时表中（更新 T2 ）。下部分支中的哈希匹配将表假脱机与旧的 T1ID 上的 T2 相连。哈希匹配到 T2 更新的输出是 T2ID 来自的聚集索引扫描T2 和表假脱机中新计算出的 T1ID（Expr1013）。
The Eager Spool in the top branch (update of T1) saves the old T1ID and the new calculated T1ID (Expr1013) to a temporary table that is used by the lower branch (update of T2). The Hash Match in the lower branch is joining the Table Spool with T2 on the old T1ID. Output from the Hash Match to the update of T2 is T2ID from the Clustered Index Scan of T2 and the new calculated T1ID (Expr1013) from the Table Spool.
更新2：
如果您需要将级联更新替换为而不是触发器，您需要有一种方法来加入已插入和已删除触发器中的表。可以使用 T1 中的代理键来完成。
If you need to replace the cascade update with a instead of trigger you need to have a way to join the inserted and deleted tables in the trigger. That can be done with a surrogate key in T1.
表：
create table T1
(
T1ID int primary key,
ID int identity unique
);
create table T2
(
T2ID int primary key,
T1ID int references T1(T1ID)
);
触发器可能看起来像这样。
The trigger could look like this.
create trigger tr_T1 on T1 instead of update as
insert into T1(T1ID)
select T1ID
from inserted;
update T2
set T1ID = I.T1ID
from inserted as I
inner join deleted as D
on I.ID = D.ID
where D.T1ID = T2.T1ID;
delete from T1
where T1ID in (
select T1ID
from deleted
);
这篇关于级联更新/删除在SQL Server内部如何工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
