Posted by CK on 03/17/06 22:34

That's much cleaner. Thanks for the tips. Works great!!!

CK

"DickChristoph" <dchristo99@yahoo.com> wrote in message
news:C0DSf.17422$iR1.5273@tornado.rdc-kc.rr.com...
> 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]