TR_ITINERARYSTAFFRESOURCE_DELETE_REQUIREDSTAFFRESOURCECANNOTBEDELETED (Trigger)

                
                    CREATE trigger [dbo].[TR_ITINERARYSTAFFRESOURCE_DELETE_REQUIREDSTAFFRESOURCECANNOTBEDELETED] 
                        on [dbo].[ITINERARYSTAFFRESOURCE] 
                        after delete not for replication
                        as begin
                        
                            if (
                                exists (
                                    select DELETEDITINERARYSTAFFRESOURCE.ID
                                    from deleted as DELETEDITINERARYSTAFFRESOURCE
                                    inner join dbo.ITINERARY on DELETEDITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
                                    inner join dbo.GROUPTYPEREQUIREDSTAFFRESOURCE on 
                                        (GROUPTYPEREQUIREDSTAFFRESOURCE.GROUPSALESGROUPTYPECODEID = ITINERARY.GROUPSALESGROUPTYPECODEID) and
                                        (DELETEDITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = GROUPTYPEREQUIREDSTAFFRESOURCE.VOLUNTEERTYPEID) and
                                        (DELETEDITINERARYSTAFFRESOURCE.FILLEDBYCODE = GROUPTYPEREQUIREDSTAFFRESOURCE.FILLEDBYCODE)
                                    where
                                        DELETEDITINERARYSTAFFRESOURCE.IGNORESQUANTITYFORCAPACITY = 0
                                )
                            )
                            begin
                                rollback transaction;
                                raiserror('BBERR_CANNOTDELETEREQUIREDRESOURCE', 13, 1);
                            end
                        
                        end