Deleting primary key when no foreign records exist?

Is there a trigger that would handle this situation? I have a 1 to
many between two tables and would like the primary key deleted if the
last foreign record is deleted.
Thanks in advance!Sure, in an AFTER trigger on the child table, something like (including a
scenario to try it out.):
set nocount on
go
create table parent
(
parentKey int primary key
)
create table child
(
childKey int primary key,
parentKey int foreign key references parent(parentKey)
)
insert into parent
select 1
union all
select 2
union all
select 3
insert into child
select 1,1
union all
select 2,1
union all
select 3,1
union all
select 4,2
union all
select 5,2
union all
select 6,2
union all
select 7,3
union all
select 8,3
go
create trigger child$deleteTrigger
on child
after delete
as
--be sure to add error handling
delete parent
--this gets all parent rows that are related to the deleted child rows based
on the migrated key from the parent
where exists (select 1
from deleted
where parent.parentKey = deleted.parentKey)
--this excludes parents where a child still exists
and not exists (select 1
from child
join deleted
on child.parentKey =
deleted.parentKey
where parent.parentKey = deleted.parentKey)
go
delete child where parentkey = 1
select *
from parent
left outer join child
on parent.parentKey = child.parentKey
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1139625347.478136.146410@.g14g2000cwa.googlegroups.com...
> Is there a trigger that would handle this situation? I have a 1 to
> many between two tables and would like the primary key deleted if the
> last foreign record is deleted.
> Thanks in advance!
>|||Many thanks Louis!|||Yes, you can do this with a trigger, if you like to write procedural,
proprietary code. Look for a COUNT(*) = 0 in a PK-FK join.
Another way to do this is to put one "child" item the same table as the
"parent" since you seem to require at least one "child" as part of the
design. When you delete the "only child", you have to delete the
parent.
Oh, did I mention that the code is messy?|||> Another way to do this is to put one "child" item the same table as the "p
arent"
Joe,
Are you saying your solution conforms to 3NF?
What if later you'll need to delete the child row stored along with the
parent? You'll have to move another row from the child table to the
parent one. Are you claiming it's less messy than Louis's solution?
Also instead of selects against the child table you'll have to select
against a union of the child and the parent, right?
How would you enforse a unique constraint on the child?sql