Microsoft SQL Server forum

Author

triggers

Sam

2005-06-10, 9:23 am

Hi,
Say I have a table with its primary key:
MyTable(MyTableId)

Now this PK is referenced by other table as a FK and therefore if I
delete from MyTable I need to delete all those references. My database
is obviously much more complex than this and I want to create a trigger
on MyTable so that it will do the job for me.

But I don't know how to write my trigger really,.... I don't know how
to get the id of the record being deleted.
Say I have a table AnotherTable that has a reference to MyTableId. I
would like to have my triggers like:

delete from AnotherTable where MyTableId = the MyTable record being
deleted.

How can I do that ?

Thx

Simon Hayes

2005-06-10, 9:23 am

If it's a simple relationship, then cascading referential integrity
might be the easiest option (assuming you have MSSQL 2000) - see
"Cascading Referential Integrity Constraints" in Books Online:

create table dbo.MyOtherTable (

.... constraint FK_MyTable foreign key (MyTableID) references MyTable
(MyTableID) on delete cascade...
)

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.

Simon

Sam

2005-06-10, 9:23 am

thx
i've used cascade delete, it's easier, simpler and it works fine with
my database.

Erland Sommarskog

2005-06-11, 7:23 am

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/techin.../2000/books.asp

Sponsored Links