Cascade Delete Using Triggers

Cascade Delete Using Triggers

Post by Terry Pin » Wed, 31 Oct 2001 10:03:10



Hi All,

I have Three tables namely Contact, Orderheader and
OrderItem. Contact is related to Orderheader via ContactID
and Orderheader is related to OrderItem via OrderID.
Now I have a Delete Trigger which sits on Contact and
deletes related rows in OrderHeader. And simarly on
OrderHeader there is a trigger which deletes related rows
in OrderItem. So the idea is that when you delete a
contact all related orderheader and orderitem rows are
removed. However I am finding that the trigger which sits
on the OrderHeader table only fires once, and I am left
with orphaned rows in the OrderItem Table.
I have checked that the Nested Triggers option is set on
the server options and I have also enabled Recursive
Triggers for the database.
We have SQL server 7 installed with Service Pack 2.

Any suggestions on how to resolve this issue would be
greatly appreciated.

Kind Regards,

Terry Pino
The Salvation Army - Australia Southern Territory

 
 
 

Cascade Delete Using Triggers

Post by BP Margoli » Wed, 31 Oct 2001 10:24:45


Terry,

A trigger fires once per DELETE statement. It does not fire on a row-by-row
basis.

Check out the section "Using the Special Inserted and Deleted Tables" in the
SQL Server 7.0 Books Online for additional information.

The following is just a very brief concept of what your trigger probably
needs to look like ... it is untested.

create trigger x on Contact for DELETE as
delete from OrderHeader
where ContactID in (select ContactID from DELETED)
go

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Hi All,

> I have Three tables namely Contact, Orderheader and
> OrderItem. Contact is related to Orderheader via ContactID
> and Orderheader is related to OrderItem via OrderID.
> Now I have a Delete Trigger which sits on Contact and
> deletes related rows in OrderHeader. And simarly on
> OrderHeader there is a trigger which deletes related rows
> in OrderItem. So the idea is that when you delete a
> contact all related orderheader and orderitem rows are
> removed. However I am finding that the trigger which sits
> on the OrderHeader table only fires once, and I am left
> with orphaned rows in the OrderItem Table.
> I have checked that the Nested Triggers option is set on
> the server options and I have also enabled Recursive
> Triggers for the database.
> We have SQL server 7 installed with Service Pack 2.

> Any suggestions on how to resolve this issue would be
> greatly appreciated.

> Kind Regards,

> Terry Pino
> The Salvation Army - Australia Southern Territory


 
 
 

Cascade Delete Using Triggers

Post by Terry Pin » Wed, 31 Oct 2001 15:12:04


Thanks for the response....you're a lifesaver.

Terry Pino

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Cascade delete Using triggers.

I come from a pure ORACLE background & am now working on SQL Server 7.0.

I see that A DELETE trigger fires *AFTER* the delete operation is
performed - unlike oracle where you could dictate if the trigger is
PRE-delete or POST-delete.

The problem I face is this. I have 2 tables A MASTER & Detail. I Wrote a
DELETE trigger that deletes from the DETAIL (before it can delete from
the parent.) -- When I delete from the parent, I get a constraint
voilation error.

I wonder if that is the limitation of SQL Server or there is some
pre-requisite I am overlooking.

please respond.

Thanks in advance.
Sunder.

2. DB reorganisation script

3. Cascade delete using triggers.

4. Internal consistency error in foxbase+ on Xenix

5. cascading delete/update using triggers with 6.5

6. Application documenter?

7. Using triggers or RI constraints to cascading updates / deletes

8. SQL tools going open source

9. delete trigger or cascade delete, which one??

10. HELP! Creating delete trigger for cascade delete

11. No INSTEAD OF DELETE triggers for tables with ON DELETE CASCADE constraints

12. ON DELETE CASCADE - does it fire delete trigger?

13. Delete Cascade & Delete Triggers