0

I am trying to figure out how to perform on update cascade on a self-referencing temporal 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
)
LifetimeLearner4706
  • 5
  • 5
  • 1
    The `Deleted` pseudo-table only exists inside the trigger - it contains the rows being deleted (in a `ON DELETE` trigger) - or the "old" rows before the `UPDATE` took place in a `ON UPDATE` trigger. It contains the exact same columns as the table the trigger is attached to – marc_s Jan 12 '22 at 08:20
  • Can you please help me modify the query if there were other foreign key constraints in the table for on update cascade? – LifetimeLearner4706 Jan 12 '22 at 09:07
  • "What if primary key is a pair of columns?" - if you have many columns, lets say ID1, ID2, ID2 then, when deleting records, instead of `WHERE col IN( .... )` you would need an `INNER JOIN`. See ["Delete With INNER JOIN"](https://stackoverflow.com/questions/16481379/how-can-i-delete-using-inner-join-with-sql-server) examples. – Alex Jan 12 '22 at 09:16
  • "Also, I am not sure why is IDs inside the CTE" - if you are referring to `;WITH IDs as` then `IDs` in this case is just a name. It can be "Bob" or anything else. – Alex Jan 12 '22 at 09:18
  • "What would the on update cascade specific trigger look like if the table has other foreign key constraints?" - you would need more delete statements e.g. `DELETE FROM OtherTable .......` and then `DELETE FROM OtherTable2 .......` etc. – Alex Jan 12 '22 at 09:20
  • Alex - I dont want to delete as shown in the below query :- -- Delete from FK referenced table DELETE FROM OtherTable WHERE CommentID in (select ID from @deletions) --This delete comes last DELETE FROM Comments WHERE id in (select ID from @deletions); I want to update two referenced columns – LifetimeLearner4706 Jan 12 '22 at 12:03
  • Have a look at [this](https://stackoverflow.com/questions/4347903/sql-server-is-it-possible-to-cascade-update-a-self-referencing-table). Give it a try yourself an post your attempt. I will have a look at this afterwards. – Alex Jan 12 '22 at 13:18
  • You can't do an `update cascade` with a trigger because there is no way to match the rows from `inserted` to the ones in `deleted`. Unless you have a *second* unique constraint which doesn't change. `delete cascade` is no problem because you don't need to worry about what to update the values to, you just delete – Charlieface Jan 13 '22 at 21:01

1 Answers1

0

Since the table is a temporal table, only 'After Update' trigger can be used on it and not the 'Instead Of Update' trigger (link for reference). I used the below solution for now to set stuff to null. This isnt a complete answer - yet trying to share with the community what I know.

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

Even though this code mimics a cascading event - it does not enfore referential integrity.

LifetimeLearner4706
  • 5
  • 5