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






Show quoteHide quote

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?

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?

Why is this statement blocking...
NESTED SQL QUESTION and aggregate function
CLR Triggers and inserted table
SQL script, split digits and letters
Concatenating:www.aspfaq.com/show.asp?id=2529 Is the glass half full or half empty or..?