Updating a row in another table via trigger

[Dan777]

How do I set up an update trigger on a child table that updates the corresponding parent row in the parent table.&nbsp;&nbsp;For example, in the child table there's a column, nProducts, which is the number of products that child provides.&nbsp;&nbsp;The parent table has a column, nTotalProducts, which is the total number of products provided by its children.&nbsp;&nbsp;So if a parent named &quot;AParent&quot; has 3 children, each of whom have 5 products, the nTotalProducts in the AParent should = 15.<br><br>So when the nProducts changes in one of the children from 5 to 6, the trigger should fire that updates the nTotalProducts in the parent row from 15 to 16.<br><br>What I want to do is something like this:<br><br>CREATE TRIGGER x<br>ON ChildTable<br>FOR UPDATE<br>AS<br>&nbsp;&nbsp;&nbsp;DECLARE @nProducts INT,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;@iParentID INT<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SET @ParentID = <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ChildTable.iForeignKeyForParentTable&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br><br>&nbsp;&nbsp;&nbsp;SET ParentTable.nTotalProducts = <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(SELECT SUM(nProducts) <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM ChildTable<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE ChildTable.iID = <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[The ID of the current child row])<br>&nbsp;&nbsp;&nbsp;&nbsp;FROM ParentTable<br>&nbsp;&nbsp;&nbsp;WHERE ParentTable.iId =&nbsp;&nbsp;@iParentID<br><br>The sole problem I'm having is getting the foreign key value from the current row that is firing the trigger.&nbsp;&nbsp;How do I reference this?<br><br>Any and all assistance with this is GREATLY appreciated.&nbsp;&nbsp;Thanks much!<br><br>Dan<br><br>

 

[balor]

In the virtual &quot;inserted&quot; table you have the updated record. You can retrive the value of the ID from there.<br><br>Do like this:<br>WHERE ChildTable.iID = inserted.iID<br><br>With A UPDATE trigger you have the new value in the virtual<br>&quot;inserted&quot; table and the old value in the virtual <br>&quot;deleted&quot; table.<br>If its an INSERT that triggers the event, only the &quot;inserted&quot; table exists, and if its a DELETE that triggers an event only the &quot;deleted&quot; table exsists.<br><br>//Martin