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
|
Trigger Definition
