How cascade Update/Delete works internally in SQL Server?

Question

Ok, I believe the question was not clear. Here i rewrite this in other way.

Suppose i create two tables,

• table1(c1 int PRIMARY KEY)

• table2(table1c11 int)

There is a relation between table1 and table2 i.e. table1.c1=table2.table1c11

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')

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.

So, later I apply a new SQL update statement in table1 i.e.

Update table1 set c1=c1+'updated'

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.

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.

So, the question arises how SQL Server handles batch update in case of the primary key data changes in the table.

Answer 1

So, the question arises how SQL Server handles batch update in case of the primary key data changes in the table.

SQL Server builds a query plan for the update statement that update both tables.

Create the tables:

create table T1
(
  T1ID int primary key
);

create table T2
(
  T2ID int primary key, 
  T1ID int references T1(T1ID) on update cascade
)

Add some data:

insert into T1 values(1), (2)
insert into T2 values(1, 1), (2, 1), (3, 2)

Update primary key of T1:

update T1
set T1.T1ID = 3
where T1.T1ID = 1

The query plan for the update looks like this:

The plan has two Clustered Index Update steps, one for T1 and one for T2.

Update 1:

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

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.

Update 2:

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.

Tables:

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 Fiddle