Posted by DickChristoph on 03/17/06 20:14

Hi CK

That looks like it should work, I would suggest adding "Begin Transaction"
at the start of the SP and "Commit Transaction" at the end. That way you can
be sure all of the rows in the four tables are either deleted or not (should
one step fail for some reason)

Additionally if TaskOrder is a very large table you might consider putting
the TaskOrderIDs to be deleted in a Temp table rather than searching the
TaskOrder Table three times, particularily if TaskOrder.BillingContractID is
not indexed.

--
-Dick Christoph
"CK" <c_kettenbach@hotmail.com> wrote in message
news:jPCSf.2355$tN3.1835@newssvr27.news.prodigy.net...
> There were no constraints defined on the tables. Can I do it this way?
>
> ALTER PROC spDeleteBillingContract (@BillingContractID INT)
> AS
> DELETE FROM TaskOrderCharge WHERE TaskOrderID IN
> (SELECT TaskOrderID From TaskOrder WHERE
> BillingContractID=@BillingContractID)
> DELETE FROM TaskOrderFunding WHERE TaskOrderID IN
> (SELECT TaskOrderID From TaskOrder WHERE
> BillingContractID=@BillingContractID)
> DELETE FROM TaskOrder WHERE BillingContractID=@BillingContractID
> DELETE FROM BillingContract WHERE BillingContractID=@BillingContractID
>
> RETURN
>
>
> "Doug Lawry" <lawry@nildram.co.uk> wrote in message
> news:dverqe$go5$1@nntp0.reith.bbc.co.uk...
>> Look up "Cascading Referential Integrity Constraints" in Transact-SQL
>> Help. This does it for you!
>> --
>> Regards,
>> Doug Lawry
>> www.douglawry.webhop.org
>>
>>
>> "CK" <c_kettenbach@hotmail.com> wrote in message
>> news:n9CSf.54699$H71.13382@newssvr13.news.prodigy.com...
>>> I have a one to many relationship between a billing table and a task
>>> table.
>>> I want to write a procedure I can pass a billingID to and it will go
>>> delete all the task records with that billingID as well as the as the
>>> single billing record. Like referential integrity.
>>>
>>> There are two other task tables taskcharge and taskfunding. When I
>>> delete a task from the task table I want to delete the related records
>>> from those two tables as well.
>>>
>>> Bottom line, I want to delete a billing record then delete then child
>>> records in the task table, as they are deleted I need to delete the
>>> taskcharge and taskfunding records child records of the task. The task
>>> table has the billingID field and a taskID field. The taskcharge and
>>> taskfunding tables have the taskID field only.
>>>
>>> Can this be done? I suggested trying triggers but this customer doesn't
>>> want to use triggers in delete operations. How do I accomplish this.
>>>
>>> Any advice is appreciated.
>>>
>>> Thanks in Advance,
>>> CK
>>
>>
>
>

[Back to original message]