title: SQL Server Parent/Child Trigger
body: <p>I have two entities in a database that behave in a parent/child relationship. </p>
  
  <ul>
  <li>A record in the parent table contains a total quantity</li>
  <li>A record(s) in the child table must sum to less-than/equal-to the parent total</li>
  </ul>
  
  <p>I have two triggers</p>
  
  <ul>
  <li>The parent table trigger checks that changes do not violate the constraint based on the child values</li>
  <li>The child table trigger checks that changes do not violate the constraint based on the parent values</li>
  </ul>
  
  <p>The business application allows the user to update the two simultaneously. For example I can add a parent total, and add child line items in the same form submission. Or I can increase/decrease both sides of existing quantities in the same transaction.</p>
  
  <p>The problem occurs when the user wants to zero-out a parent and zero-out all its children. The transaction starts by attempting to zero out the parent, which triggers a violation, and rollback the transaction.</p>
  
  <p>So you may say just invert the order. But if I try to insert children without inserting the parent first, then I will also violate the other trigger in situations where the user is creating new records. </p>
  
  <p>What I'm looking for is if there is something equivalent to a deferred trigger (which I don't think there is). I could also remove the triggers altogether, enforce the rules within the SP, but is that the best option here? I'm hesitant to leave the tables open like that without checks on the data.</p>
  
  <p><strong>Edit- Added Trigger Sample</strong></p>
  
  <p>The column names aren't exact but the logic is identical to what I'm using. </p>
  
  <pre><code>CREATE TRIGGER trg_child ON tbl_child
  AFTER UPDATE,INSERT AS
  
  BEGIN
  
  IF EXISTS (SELECT PARENT_ID,ParentQty FROM (SELECT PARENT_ID, SUM(QTY) AS ChildQty FROM INSERTED GROUP BY PARENT_ID) 
          JOIN tbl_parent WHERE ChildQTY&gt;ParentQty)
  BEGIN
      RAISERROR('Child quantities exceed parent',16,1)
      ROLLBACK TRANSACTION
  END 
  ELSE
  END
  </code></pre>