On update cascade for a self-referencing key

I am trying to figure out how to perform on update cascade on a self-referencing table using triggers. While I found that this post (On delete cascade for self-referencing table) is the closest it can get to my answer, I had the below questions:

The Answer and the question seems incomplete in the post. Can you please tell me what is contained in Deleted table in the post? What is id in Deleted table and Comments table? Is it a primary key? What if primary key is a pair of columns? Also, I am not sure why is IDs inside the CTE IDs. Seems incorrect. I am not sure.

What would the on update cascade specific trigger look like if the table has other foreign key constraints?

I have the below table setup. Can you please help me create a trigger for it on update cascade on the foreign key FK_Son_Height_Weight? I could use a surrogate key here but there are several tables that have foreign key reference to PK_Height_Weight. Is there a way to make sure I dont need a surrogate key?

(Note: the table has been modified for privacy reasons)

CREATE TABLE no.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
)

CodePudding user response：

Will the below code work?

CREATE TRIGGER no.Height_Weight
    ON no.Man
    INSTEAD OF UPDATE
AS
    Set nocount on
declare @updations table (Height varchar(100), Weight 
varchar(50), Son_Height varchar(100), Son_Weight varchar(50));
;WITH IDs as (
   select Height, Weight, Son_Height, Son_Weight from inserted
   union all
   select b.Height, b.Weight, b.Son_Height, b.Son_Weight
   from no.Man b 
          inner join
        IDs i
          on
             b.Height = i.Height
             AND b.Weight = i.Weight
)
insert into @updations(Height, Weight, Son_Height, Son_Weight)
select Height, Weight, Son_Height , Son_Weight from IDs

    UPDATE mn
        SET Son_Height = u.Son_Height,
        Son_Weight= u.Son_Weight
        FROM @updations u
            INNER JOIN no.Man mn
                ON u.Height = mn.Height and u.Weight = mn.Weight

CodePudding user response：

I later found out that my table was a temporal table and I could use only the 'After Update' trigger on it and not the 'Instead Of Update' trigger (link for reference). I used the below solution for now to set stuff to null rather than use the recursive solution above because of my hesitation to use recursion. This works for me.

CREATE or ALTER TRIGGER no.Weight_Height
ON no.Man
After UPDATE
AS
Set nocount on

;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