Posted by CK on 03/17/06 20:00

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]