TR_EVENTSTAFFRESOURCE_DELETE_JOB

                    CREATE trigger [dbo].[TR_EVENTSTAFFRESOURCE_DELETE_JOB] on [dbo].[EVENTSTAFFRESOURCE] after delete not for replication
                        as begin
                            delete from JOBOCCURRENCE
                                from deleted
                                where deleted.JOBOCCURRENCEID = JOBOCCURRENCE.ID
                                    and not exists (select id from EVENTSTAFFRESOURCE where JOBOCCURRENCEID = deleted.JOBOCCURRENCEID)
                                    and not exists (select id from ITINERARYSTAFFRESOURCE where JOBOCCURRENCEID = deleted.JOBOCCURRENCEID)
                                    and not exists (select id from ITINERARYITEMSTAFFRESOURCE where JOBOCCURRENCEID = deleted.JOBOCCURRENCEID)

                            delete from JOB
                                from deleted
                                where deleted.JOBID is not null
                                    and deleted.JOBID = JOB.ID
                                    and not exists (select id from PROGRAMSTAFFRESOURCE where JOBID = deleted.JOBID)
                                    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