By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,197 Members | 933 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,197 IT Pros & Developers. It's quick & easy.

triggers

P: n/a
Sam
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

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Jul 23 '05 #2

P: n/a
Sam
thx
i've used cascade delete, it's easier, simpler and it works fine with
my database.

Jul 23 '05 #3

P: n/a
Simon Hayes (sq*@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

P: n/a
Simon Hayes (sq*@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.