TR_ITINERARYITEM_DELETE_ITINERARYSTAFFRESOURCEUPDATE

                    CREATE trigger [dbo].[TR_ITINERARYITEM_DELETE_ITINERARYSTAFFRESOURCEUPDATE] 
                        on [dbo].[ITINERARYITEM] 
                        after delete not for replication
                    as begin
                        declare @CURRENTDATE datetime;                                        
                        set @CURRENTDATE = GetDate();

                        /* Create a map of JOBOCCURRENCEIDs to ITINERARYITEMSTAFFRESOURCEIDs 
                            so we can set the JOBOCCURRENCEID on the ITINERARYITEMSTAFFRESOURCE. */
                        declare @JOBMAP TABLE
                        (
                            RECORDID uniqueidentifier,
                            JOBOCCURRENCEID uniqueidentifier,
                            ITINERARYID uniqueidentifier,
                            CHANGEDBYID uniqueidentifier
                        )

                        /* First items that have changed the itinerary such that it no longer has any items. */
                        insert into @JOBMAP
                            (RECORDID, JOBOCCURRENCEID, ITINERARYID, CHANGEDBYID)
                        select ITINERARYSTAFFRESOURCE.ID, ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID, ITINERARY.ID, deleted.CHANGEDBYID
                        from deleted
                            inner join ITINERARY
                                on deleted.ITINERARYID = ITINERARY.ID
                            inner join dbo.ITINERARYSTAFFRESOURCE
                                on ITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
                        where ((ITINERARY.STARTDATETIME is null) and (ITINERARY.ENDDATETIME is null))

                        /* Update the itinerary staff resource records to have a null JOBOCCURRENCEID */
                        update dbo.ITINERARYSTAFFRESOURCE
                            set JOBOCCURRENCEID = null,
                                CHANGEDBYID = JOBMAP.CHANGEDBYID,
                                DATECHANGED = @CURRENTDATE
                        from dbo.ITINERARYSTAFFRESOURCE
                            inner join @JOBMAP as JOBMAP
                                on ITINERARYSTAFFRESOURCE.ID = JOBMAP.RECORDID

                        /* now delete the job occurences */
                        delete from dbo.JOBOCCURRENCE
                        from @JOBMAP as JOBMAP
                        where JOBOCCURRENCE.ID = JOBMAP.JOBOCCURRENCEID

                        /* Now we need to handle items where the removal has changed the start or end date/time of the itinerary */
                        delete from @JOBMAP;

                        insert into @JOBMAP
                            (RECORDID, JOBOCCURRENCEID, ITINERARYID, CHANGEDBYID)
                        select ITINERARYSTAFFRESOURCE.ID, ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID, ITINERARY.ID, deleted.CHANGEDBYID
                        from deleted
                            inner join ITINERARY
                                on deleted.ITINERARYID = ITINERARY.ID
                            inner join dbo.ITINERARYSTAFFRESOURCE
                                on ITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
                        where ((ITINERARY.STARTDATETIME > deleted.STARTDATETIME) or
                                (ITINERARY.ENDDATETIME < deleted.ENDDATETIME))

                        /* Update the job occurrences with the new start and end date/time */
                        update dbo.JOBOCCURRENCE
                            set STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(ITINERARY.STARTDATETIME),
                                ENDDATE = dbo.UFN_DATE_GETEARLIESTTIME(ITINERARY.ENDDATETIME),
                                STARTTIME = dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.STARTDATETIME),
                                ENDTIME = dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.ENDDATETIME),
                                CHANGEDBYID = JOBMAP.CHANGEDBYID,
                                DATECHANGED = @CURRENTDATE
                        from dbo.JOBOCCURRENCE
                            inner join @JOBMAP as JOBMAP
                                on JOBOCCURRENCE.ID = JOBMAP.JOBOCCURRENCEID
                            inner join dbo.ITINERARY
                                on JOBMAP.ITINERARYID = ITINERARY.ID
                        where (STARTDATE <> dbo.UFN_DATE_GETEARLIESTTIME(ITINERARY.STARTDATETIME)) or
                                (ENDDATE <> dbo.UFN_DATE_GETEARLIESTTIME(ITINERARY.ENDDATETIME)) or
                                (STARTTIME <> dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.STARTDATETIME)) or
                                (ENDTIME <> dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.ENDDATETIME))
                    end