title: On update cascade for a self-referencing key - SQL Server
body: <p>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 (<a href="https://stackoverflow.com/questions/42228082/on-delete-cascade-for-self-referencing-table">On delete cascade for self-referencing table</a>) is the closest it can get to my answer, I had the below questions:</p>
  <p>The Answer and the question seems incomplete in the post. Can you please tell me what is contained in <code>Deleted</code> table in the post? What is <code>id</code> in <code>Deleted</code> table and <code>Comments</code> 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.</p>
  <p>What would the on update cascade specific trigger look like if the table has other foreign key constraints?</p>
  <p>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?</p>
  <p>(Note: the table has been modified for privacy reasons)</p>
  <p>CREATE TABLE no.Man (</p>
  <pre><code>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
  )
  </code></pre>