TR_PROGRAMSTAFFRESOURCE_DELETE_JOB

                    CREATE trigger [dbo].[TR_PROGRAMSTAFFRESOURCE_DELETE_JOB] on [dbo].[PROGRAMSTAFFRESOURCE] after delete not for replication
                        as begin                            
                            delete from JOB
                                from deleted
                                where deleted.JOBID = JOB.ID
                                    and not exists (select id from EVENTSTAFFRESOURCE where JOBID = deleted.JOBID)
                                    and not exists (select id from TRACKSTAFFRESOURCE where JOBID = deleted.JOBID)
                                    and not exists (select id from TRACKITEMSTAFFRESOURCE where JOBID = deleted.JOBID)
                                    and not exists (select id from ITINERARYSTAFFRESOURCE where JOBID = deleted.JOBID)
                                    and not exists (select id from ITINERARYITEMSTAFFRESOURCE where JOBID = deleted.JOBID)
                        end