CODEWITHSUNDEEP
sql-servertriggerscursors
bredmann
bredmann

Reputation: 1

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

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.

Upvotes: 0

Views: 13011

Answers (2)

Homie
Homie

Reputation: 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

Upvotes: -1

M.Ali
M.Ali

Reputation: 69554

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

Upvotes: 2

Related Questions