How do I delete the master record automatically when all detail records are deleted in SQL Server 2008?

Question

Say I have two tables. Errors and ErrorsDetails. So I have one Errors record that could contain many ErrorDetails records.

The users will periodically delete the detail records through an application they use. When all detail records are cleaned up (deleted), I would like to automatically delete the master record too.

What would be cool would be one SQL delete statement that would delete the details and the master if no more details are owned by the master. But I can't seem to get a clean SQL statement that does that. I'd rather not poll the master for details after every delete.

Errors

ID        Error
1         Import Error
2         Export Error

ErrorsDetails

ID        ErrorsID        DetailedInfo
1         1               Couldn't import file because of FTP error
2         1               Couldn't import file because of permission error
3         2               Couldn't export file because of directory doesn't exist

I've tried adding a cascade delete constraint like so but it didn't seem to work.

alter table dbo.ErrorsDetails
    add constraint FK_Delete_Master
        foreign key (ErrorsID) references dbo.Errors(ErrorsID) on delete cascade

Thanks for any suggestions.

UPDATE

It's been mentioned to use triggers. I thought about that but didn't mention it. A trigger would certainly do the job. Pros/Cons of triggers for this scenario?

Answer 1

You can use triggers or

declare @ErrorsId int
select @ErrorsId = ErrorsId from ErrorsDetails where ID = @id
delete from ErrorsDetails where ID = @id

declare @errorDetailCount int
select @errorDetailCount = count(*) from ErrorsDetails where ErrorsId = @ErrorsId 

if (@errorDetailCount = 0)
begin
 delete from Errors where ID = @ErrorsId 
end

UPDATED: trigger:

create trigger [dbo].[ErrorsDetails_Delete_Trigger] on [dbo].[ErrorsDetails] 
instead of delete
as 
begin          
    declare @ErrorsId int
    select @ErrorsId = ErrorsId from deleted
    delete from ErrorsDetails where ID = deleted.ID

    declare @errorDetailCount int
    select @errorDetailCount = count(*) from ErrorsDetails where ErrorsId = @ErrorsId 

    if (@errorDetailCount = 0)
    begin
     delete from Errors where ID = @ErrorsId 
    end     
end