tsql - T-SQL delete cascade (or trigger) with multiple references to child table -

the problem larger, in nutshell. have 2 tables invoice , address.

the invoice table has 2 columns:

• billingaddressid int, fk address(addressid)
• shippingaddressid int, fk address(addressid)

i declare both of these relationships on delete cascade, can't because causes "multiple cascade paths" error. need able delete invoice record , have delete both address records.

so instead, create trigger:

create trigger [dbo].[trigger_invoice_deleteaddress] on [dbo].[invoice] delete begin     set nocount on      delete address      addressid in (select billingaddressid deleted);      delete address      addressid in (select invoiceaddressid deleted); end 

this flat out doesn't work, get:

the delete statement conflicted reference constraint "fk_invoice_billingaddress".
conflict occurred in database "example", table "dbo.invoice", column 'billingaddressid'.

also tried suggestion:

create trigger [dbo].[trigger_invoice_deleteaddress] on [dbo].[invoice] instead of delete begin     set nocount on      delete address      addressid in (select billingaddressid deleted);      delete address      addressid in (select invoiceaddressid deleted);      delete invoice     invoiceid in (select invoiceid deleted);  end 

this has same error.

what have missed?

your problem because trying delete parent automatically when delete child. may not possible because parent linked invoice. in event not idea. need revisit requirement , see if makes sense do. have never seen system (and have worked hundreds) deletion of child automatically triggered deletion of parent record.

in case bad. if customer , cancelled invoice , went buy few days later , had re-enter address information gave previously, annoyed , far less want continue business company foolish enough such thing. type of requirement drives business away. why on earth woudl want delete address invoice? why on earth deleting invoices @ all? accounting systems never ever delete invoices. if need cancel them, them out offsetting charge. whole idea open system fraud no 1 person should ever allowed such thing, bad idea. need reading on accounting standards , on internal controls.

you can't delete parent record until child record gone, why triggers not working you.

i suggest deleting in stored procedure or alternatively, have trigger put address id records in staging table , delete them job runs every 5 minutes. in job, make sure check see if there other invoices attached address , delete when there none left. still think deleting either addresses or invoices bad idea.