Link to home
Start Free TrialLog in
Avatar of MHDMarkei
MHDMarkei

asked on

Trigger on Delete performance

I need to create a Trigger on a delete from a table so that related rows are also deleted. I am aware of the 'cascade' feature but for logistic reasons cannot be used in this case. TableB can contain many rows for each unique tableA row being deleted. Note that the rows in tableA are deleted by a user application and can result in multiple rows being deleted.
e.g DELETE FROM tableA WHERE tableAKeyCol2 = <value>

The two options being considered as a trigger on DELETE from TableA are follows: -

        DELETE FROM tableB
          WHERE EXISTS (SELECT tableAKeyCol FROM DELETED WHERE tableAKeyCol = tableBKeyCol)
OR        
        DELETE FROM tableB WHERE tableBKeyCol IN  (SELECT tableAKeyCol FROM DELETED )

It would appear that there is no difference in end result, however, which is the preferred approach and why?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial