TR_ITINERARYITEM_DELETE_ITINERARYSTAFFRESOURCEUPDATE

Trigger Definition

				
					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