title: SQL Server for delete trigger to delete records in other table with procedure
body: <p>I've got a table like <code>table1</code> (<code>table1_id, col1, some_ID</code>) and another <code>table2</code> (<code>table2_id, col1, table1_id</code>)</p>
  
  <p>I have a procedure </p>
  
  <pre><code>PROC deleteTable2(@id int)
  AS
  BEGIN 
      DELETE table2 
      WHERE table1_id = @id
  END
  </code></pre>
  
  <p>I'm trying to create a trigger for delete for <code>table1</code> table, so when I'm trying to delete from <code>table1</code>, the trigger will delete all records in <code>table2</code>.</p>
  
  <p>I wrote the trigger like this:</p>
  
  <pre><code>CREATE TRIGGER deleteTable1 on table1
  FOR DELETE
  AS
  BEGIN
      SET NOCOUNT ON
  
      DECLARE @id int = 0
  
      SELECT @id = del.table1_ID from deleted as del
  
      EXECUTE dbo.deleteTable2 @id
  </code></pre>
  
  <p>It works if there is only one record for <code>some_ID</code>, but if there are more records, it doesn't work. For example: </p>
  
  <pre><code>delete table1 
  where some_ID='some value'
  </code></pre>
  
  <p>This SQL query will delete all records in table1 and only first in table2.</p>
  
  <p>It makes sense, so I made a trigger with CURSOR, something like</p>
  
  <pre><code>CREATE TRIGGER deleteTable1 ON table1
  FOR DELETE
  AS
  BEGIN
      DECLARE @id int = 0, @CURSOR cursor
  
      SET @CURSOR = CURSOR scroll FOR 
         SELECT deleted.table1_id 
         FROM deleted
  
      OPEN @CURSOR
      FETCH NEXT FROM @CURSOR into @id
  
      WHILE @@FETCH_STATUS = 0
      BEGIN
            EXECUTE dbo.deleteTable2 @id
            FETCH NEXT FROM @CURSOR into @id
      END
      CLOSE @CURSOR
  END
  </code></pre>
  
  <p>But it doesn't work at all... Maybe I just missed something or I don't get some nuances. Thank you.</p>
  
  <p>UPDATE</p>
  
  <p>In fact, This 2 tables are in one db, but I've got 3rd table <code>table3 (table3_id, col1, table2_id)</code>. And this <code>table3</code> is in other db, and they connected by linked server. So when I call stored procedure as I'm trying, this stored procedure calls stored procedure (with  cursor) to delete records in <code>table3</code>. That the exact reason why I'm trying to use stored procedure to delete records in table2.</p>