|
|
Posted by Erland Sommarskog on 09/29/24 11:18
Simon Hayes (sql@hayes.ch) writes:
> But if the relationships between your tables are more complex, or if
> you need to do something extra such as move the deleted rows to an
> audit table, then a trigger would probably be better. Since triggers
> fire once per statement, not per row, you need to write them to handle
> multiple rows:
>
> create trigger dbo.MyTrigger
> on dbo.MyTable after delete
> as
> if @@rowcount = 0 return
>
> delete from dbo.MyOtherTable
> from dbo.MyOtherTable mot
> join deleted d
> on mot.MyTableID = d.MyTableID
>
> See CREATE TRIGGER in BOL for the details of the deleted and inserted
> logical tables.
Using a trigger would require you to remove the FK constraint, and have
the FK relation implemented as a trigger as well.
Alternatively, the trigger should be an INSTEAD OF trigger, which first
cascades deletes to daughter tables, and then the deletes the target table.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|