title: How to update a table inside AFTER INSERT trigger on different table?
body: <p>I have a table <code>DISPENSE_DOSAGES</code> and created an <code>AFTER INSERT</code> trigger on this table. Inside that trigger, I am trying to update another table <code>DISPENSE_DRUGS</code> having matching rows with <code>INSERTED</code> table. when a row is inserted into <code>DISPENSE_DOSAGES</code> trigger fired successfully but it does not update rows in <code>DISPENSE_DRUGS</code>. I know this line is generating issue.</p>
  
  <pre><code>WHERE [DISPENSE_ID] IN (SELECT [DISPENSE_ID] FROM INSERTED)
  </code></pre>
  
  <p><code>(SELECT [DISPENSE_ID] FROM INSERTED)</code> line in the above code is generating problem. </p>
  
  <p>I know trigger called for batch inserts and tried a cursor solution but it does not work for me.</p>
  
  <p>Here is the code for trigger</p>
  
  <pre><code>CREATE TRIGGER [dbo].[TRG_DISPENSE_DOSAGES]
  ON [dbo].[DISPENSE_DOSAGES] 
  AFTER INSERT     
  AS
  BEGIN
      IF (EXISTS (SELECT [DISPENSE_ID]
                  FROM INSERTED
                  WHERE [DISPENSE_ID] IN (SELECT [DISPENSE_ID] 
                                          FROM [DISPENSE_DOSAGES]
                                          WHERE [SEPARATE_ITEM] = 1)))
      BEGIN
          UPDATE [dbo].[DISPENSE_DRUGS]
          SET QUANTITY_DISPENSED = 0,
              QUANTITY_OWED = QUANTITY_PRESCRIBED
          WHERE [DISPENSE_ID] IN (SELECT [DISPENSE_ID] FROM INSERTED)  --This line generates issue
      END
  END
  </code></pre>