Re: Best way to represent a many to many with optionality?
- From: "....DotNet4Ever...." <hate.spam@xxxxxxxxxxx>
- Date: Wed, 27 May 2009 11:21:13 +0200
By the way, the trigger does not "compile" when I try to create it in Management Studio. The error I am given is that it does not recognize "deleted" or that the "multipart identifier deleted could not be found".
I tried with both FOR DELETE and AFTER DELETE but no luck. Also remember that the FK constraints for the two relationships (shipping & mailing address) are set to NO ACTION, meaning by default SQL Server would not permit the delete. I am confused as to how that works in conjunction with the delete trigger.
"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message news:eyhvusd3JHA.5728@xxxxxxxxxxxxxxxxxxxxxxx
Hi
One method is using triggers
create trigger on Addresses for delete
as
if exists (select * from deleted join customers
on deleted.addressid=customers.ShippingAddressID)
begin
update custromers set ShippingAddressID=null
where exists (select * from deleted where deleted.addressid=
custromers.ShippingAddressID)
end
if exists (select * from deleted join customers
on deleted.addressid=customers.MailingAddressID )
begin
update custromers set MailingAddressID =null
where exists (select * from deleted where deleted.addressid=
custromers.MailingAddressID )
end
"....DotNet4Ever...." <hate.spam@xxxxxxxxxxx> wrote in message news:umjVbkd3JHA.140@xxxxxxxxxxxxxxxxxxxxxxxI was hoping to have an answer by now but for some reason my post from last week never made it to the server... Here is the situation I want to have but have run aground in trying to implement it properly so suggestions are welcome (and not, it is NOT in SQL Syntax for the syntacticly obsessive!).
Table Customers {
PK CustomerID bigint identity(1,1),
FK AddressID NOT NULL, -- the main (home/business) address of a customer
FK ShippingAddressID NULL, -- customer's shipping address or NULL if same as main address
FK MailingAddressID NULL -- customer's mail address or NULL if same as main address
}
Table Addresses {
PK AddressID bigint identity(0,1),
Address1 nvarchar(75) NOT NULL,
Address2 nvarchar(75) NULL,
PostalCode nvarchar(20) NOT NULL,
.... blah blah blah
}
Foreign Key Relationships:
FK_Customers_Addresses : Customers.AddressID -> Addresses.AddressID
FK_Customers_Addresses_Shipping : Customers.ShippingAddressID -> Addresses.AddressID
FK_Customers_Addresses_Mailing : Customers.MailingAddressID -> Addresses.AddressID
So far so good. I tried to implement the foreign key constraints with DELETE and INSERT/UPDATE Rules but kept on getting all sort of errors about "circular" problems (which I did not see as circular), etc.
Basically the logic I wanted behind this was:
a) The main address is compulsory (Customers.AddressID)
b) The shipping and maling addresses are optional, they are NULL by default unless it is chosen to
specify a different address than the main address. So a single customer may have 1..3 different
addreses on the Addresses table.
c) If the record corresponding to the Shipping OR Mailing address is DELETED the corresponding column in
the Customer's record should automatically be set to NULL (i.e. the FK rules).
d) If the record corresponding to the Shipping OR Mailing adress is UPDATED the change should "cascade"
to the FK column on the Customers table
e) If the record corresponding to the MAIN address is UPDATED the change should "cascade" to the FK
on the FK column Customers.AddressID
f) If an attempt to DELETE record corresponding to the MAIN address is made the action should be prohibited
by SQL (foreign key violation).
So my question is what is the proper way to set the INSERT/UPDATE and DELETE Rules of the foreign key to enforce the above mentioned "business rules" without getting the "circularity/default" errors? SQL Server lets you specify any of the following rules to apply to either INSERT/UPDATE or DELETE:
1. None (???)
2. Set to Default - (tried that but then I was getting errors about not being to do it on Shipping/Mailing if there was
3. Set to NULL
4. Cascade
Suggestions are greatly appreciated as I tried all sort of things I could imagine without being able to enforce all rules above.
Emil
a default constraint or something like that.
.
- References:
- Best way to represent a many to many with optionality?
- From: ....DotNet4Ever....
- Re: Best way to represent a many to many with optionality?
- From: Uri Dimant
- Best way to represent a many to many with optionality?
- Prev by Date: Re: Sending Messages to Outgoing Queue - SQl Server 2008
- Next by Date: "Were all exists in other table"-join
- Previous by thread: Re: Best way to represent a many to many with optionality?
- Next by thread: Re: Best way to represent a many to many with optionality?
- Index(es):
Relevant Pages
|
