Delete trigger run for each deleted row when delete multi row on edit tab

38 Views Asked by At

I have a trigger like this:

ALTER TRIGGER dbo.TG_a 
ON dbo.a
FOR DELETE
AS 
BEGIN
    SELECT * 
    INTO dbo.b  
    FROM deleted
END

When I delete multiple rows in the edit tab, there will be a problem:

There is already object name 'b'

When the trigger looks like this:

ALTER TRIGGER dbo.TG_a
ON dbo.a
FOR DELETE
AS 
BEGIN
    INSERT INTO dbo.b 
        SELECT * 
        FROM deleted
END

When I delete multiple rows in edit tab, there will be multiple duplicate values.

Problems:

  • I realise that the trigger is firing for each row when I delete multiple rows on edit tab.
  • If I delete multiple rows by query, the trigger will fire for all rows once
  • This only happen when delete because of: can not insert, update multi row on edit tab

Question:

  • I usually edit value on edit tab so I need some way to make trigger fire for all rows that are deleted on edit tab
0

There are 0 best solutions below