Ok, I'm adding a new solution since this is a different situation.
Anyhow, if I understand this correctly you want to delete the row in
mails
if no more
mymails
rows are referring to it. And the
mailid
is the same in both tables so it's a foreign key. So you're deleting the parent row when no more children exist.
If that is correct, the code could be something like:
create trigger manage_mail
on mymail
after delete
as
begin
delete from mails
where mails.mailid in (select d.mailid
from deleted d)
and not exists (select 1
from mymail mm
where mm.mailid = mails.mailid);
end
Test this carefully (inside a transaction) before you use it.
But basically this should delete all the mails rows which have the same id as in deleted rows and no more children exist.