SQL Server for delete trigger to delete records in other table with procedure

Question

I've got a table like table1 (table1_id, col1, some_ID) and another table2 (table2_id, col1, table1_id)

I have a procedure

PROC deleteTable2(@id int)
AS
BEGIN 
    DELETE table2 
    WHERE table1_id = @id
END

I'm trying to create a trigger for delete for table1 table, so when I'm trying to delete from table1, the trigger will delete all records in table2.

I wrote the trigger like this:

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

It works if there is only one record for some_ID, but if there are more records, it doesn't work. For example:

delete table1 
where some_ID='some value'

This SQL query will delete all records in table1 and only first in table2.

It makes sense, so I made a trigger with CURSOR, something like

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

But it doesn't work at all... Maybe I just missed something or I don't get some nuances. Thank you.

UPDATE

In fact, This 2 tables are in one db, but I've got 3rd table table3 (table3_id, col1, table2_id). And this table3 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 table3. That the exact reason why I'm trying to use stored procedure to delete records in table2.

PLEASE .... a trigger should be fast, small and nimble - everything a cursor definitely is NOT!! Please don't kill your system by using a CURSOR (of all things) inside a trigger! That's just awful...... use a set-based approach shown by @M.Ali in his response - always. Cursors inside a triggers is the worst nightmare for any SQL Server database! Never ever under any circumstances do that! — marc_s
– marc_s, Commented Nov 1, 2015 at 21:32

M.Ali · Accepted Answer · 2015-11-02 00:45:32Z

2

Avoid using all these unnecessary cursors and stored procedure. Simply follow a set based approach and do the following inside your trigger.

CREATE TRIGGER deleteTable1 on table1
FOR DELETE
AS
BEGIN
SET NOCOUNT ON

Delete FROM Table2 
WHERE Exists (SELECT 1 
              FROM deleted del
              WHERE del.table1_ID = table2.Table1_ID)             

END

UPDATE

Since you have mentioned you would like to delete records from a third table too which is some where on a linked server.

I would suggest you to use the same approach and just add another delete statement in inside the procedure for that third table on the linked server.

CREATE TRIGGER deleteTable1 on table1
FOR DELETE
AS
BEGIN
SET NOCOUNT ON

Delete FROM Table2 
WHERE Exists (SELECT 1 
              FROM deleted del
              WHERE del.table1_ID = table2.Table1_ID)             

DELETE FROM t3
FROM [LinkedServerName].[DBName].[SchemaName].Table3 t3 
WHERE Exists (SELECT 1 
              FROM deleted del
              WHERE del.table1_ID = t3.Table1_ID)             


END

edited Nov 2, 2015 at 0:45

answered Nov 1, 2015 at 19:13

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bredmann Over a year ago

table3 is connected with table2, not table1. Moreover, I'm pretty sure that the trigger won't work, because of a "Transaction context in use by another session" issue. Because we can't delete from other db like that in MS Sql.

Brian Tompsett - 汤莱恩 · Accepted Answer · 2019-05-06 16:05:09Z

-1

CREATE TRIGGER deleteTable1 on table1
FOR DELETE
AS
BEGIN
    SET NOCOUNT ON
    DELETE FROM table2 WHERE table1_id IN (SELECT table1_id FROM deleted)
END

edited May 6, 2019 at 16:05

Brian Tompsett - 汤莱恩

5,92772 gold badges63 silver badges135 bronze badges

answered May 6, 2019 at 14:15

Homie

1

Collectives™ on Stack Overflow

SQL Server for delete trigger to delete records in other table with procedure

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related