CREATE trigger [dbo].[TR_ITINERARYITEM_INSERTUPDATE_ITINERARYSTAFFRESOURCEUPDATE]
on [dbo].[ITINERARYITEM]
after update, insert not for replication
as begin
/* Before a row is inserted here the job needs to exist.
This trigger merely makes sure that there are proper job occurences for this job. */
/* We need some data for the inserts that are going to follow. */
declare @CURRENTDATE datetime;
set @CURRENTDATE = GetDate();
/* Create a map of JOBOCCURRENCEIDs to ITINERARYSTAFFRESOURCEIDs
so we can set the JOBOCCURRENCEID on the ITINERARYSTAFFRESOURCE. */
declare @JOBMAP TABLE
(
RECORDID uniqueidentifier,
JOBOCCURRENCEID uniqueidentifier,
ITINERARYID uniqueidentifier,
CHANGEDBYID uniqueidentifier
)
/* Make sure that we only have those itinerary staff resources attached to an itinerary
where one of the modified itinerary items defines the start or end date/time of
the itinerary. This makes sure we do not recalc data that we do not need to. */
insert into @JOBMAP
(RECORDID, JOBOCCURRENCEID, ITINERARYID, CHANGEDBYID)
select ITINERARYSTAFFRESOURCE.ID, ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID, ITINERARY.ID, inserted.CHANGEDBYID
from inserted
inner join dbo.ITINERARY
on ITINERARY.ID = inserted.ITINERARYID
inner join dbo.ITINERARYSTAFFRESOURCE
on ITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
where (not ITINERARYSTAFFRESOURCE.JOBID is null) and
(ITINERARYSTAFFRESOURCE.FILLEDBYCODE = 0) and
((ITINERARY.STARTDATETIME = inserted.STARTDATETIME) or
(ITINERARY.ENDDATETIME = inserted.ENDDATETIME))
/* First we update the existing job occurences */
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))
/* Second we need to create any new job occurences */
/* first remove the updated items from the table and get new IDs */
delete from @JOBMAP
where (not JOBOCCURRENCEID is null);
update @JOBMAP set JOBOCCURRENCEID = newid();
/* Now insert some new job occurences */
insert into dbo.JOBOCCURRENCE
(ID,
JOBID,
DESCRIPTION,
VOLUNTEERSNEEDED,
TYPECODE,
STARTDATE,
ENDDATE,
STARTMONTHDAY,
ENDMONTHDAY,
STARTTIME,
ENDTIME,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select
JOBMAP.JOBOCCURRENCEID,
ITINERARYSTAFFRESOURCE.JOBID,
left(JOB.NAME, 30),
ITINERARYSTAFFRESOURCE.QUANTITYNEEDED,
0 as TYPECODE,
dbo.UFN_DATE_GETEARLIESTTIME(ITINERARY.STARTDATETIME),
dbo.UFN_DATE_GETEARLIESTTIME(ITINERARY.ENDDATETIME),
'0000' as STARTMONTHDAY,
'0000' as ENDMONTHDAY,
dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.STARTDATETIME),
dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.ENDDATETIME),
JOBMAP.CHANGEDBYID,
JOBMAP.CHANGEDBYID,
@CURRENTDATE,
@CURRENTDATE
from @JOBMAP as JOBMAP
inner join dbo.ITINERARYSTAFFRESOURCE
on ITINERARYSTAFFRESOURCE.ID = JOBMAP.RECORDID
inner join dbo.JOB
on JOB.ID = ITINERARYSTAFFRESOURCE.JOBID
inner join dbo.ITINERARY
on ITINERARY.ID = JOBMAP.ITINERARYID
/* Now update the ITINERARYSTAFFRESOURCE table with the correct JOBOCCURRENCEIDs */
update dbo.ITINERARYSTAFFRESOURCE
set ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID = JOBMAP.JOBOCCURRENCEID,
ITINERARYSTAFFRESOURCE.CHANGEDBYID = JOBMAP.CHANGEDBYID,
ITINERARYSTAFFRESOURCE.DATECHANGED = @CURRENTDATE
from dbo.ITINERARYSTAFFRESOURCE
inner join @JOBMAP as JOBMAP
on JOBMAP.RECORDID = ITINERARYSTAFFRESOURCE.ID
where (ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID is null) or
(ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID != JOBMAP.JOBOCCURRENCEID)
/*
* Now check to see if we had any itinerary staff resources of type volunteer with
* volunteers assigned. If so we need to migrate them from the ITINERARYSTAFFRESOURCEASSIGNMENT
* table to the VOLUNTEERASSIGNMENT table.
*/
if (exists (select ITINERARYSTAFFRESOURCEASSIGNMENT.ID
from dbo.ITINERARYSTAFFRESOURCEASSIGNMENT
inner join dbo.ITINERARYSTAFFRESOURCE
on ITINERARYSTAFFRESOURCEASSIGNMENT.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID
inner join inserted
on inserted.ITINERARYID = ITINERARYSTAFFRESOURCE.ITINERARYID
where (ITINERARYSTAFFRESOURCE.FILLEDBYCODE = 0)))
begin
/* Insert the new records in the VOLUNTEERASSIGNMENTTABLE */
insert into dbo.VOLUNTEERASSIGNMENT
(ID,
VOLUNTEERID,
JOBOCCURRENCEID,
DATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select newid(),
ITINERARYSTAFFRESOURCEASSIGNMENT.CONSTITUENTID,
ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID,
dbo.UFN_DATE_GETEARLIESTTIME(ITINERARY.STARTDATETIME),
inserted.CHANGEDBYID,
inserted.CHANGEDBYID,
@CURRENTDATE,
@CURRENTDATE
from dbo.ITINERARYSTAFFRESOURCEASSIGNMENT
inner join dbo.ITINERARYSTAFFRESOURCE
on ITINERARYSTAFFRESOURCEASSIGNMENT.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID
inner join inserted
on inserted.ITINERARYID = ITINERARYSTAFFRESOURCE.ITINERARYID
inner join dbo.ITINERARY
on ITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
where (ITINERARYSTAFFRESOURCE.FILLEDBYCODE = 0)
if exists (select top 1 1 from INSERTED)
begin
declare @CHANGEAGENTID uniqueidentifier
select top 1 @CHANGEAGENTID = CHANGEDBYID from INSERTED;
declare @CONTEXTCACHE varbinary(128) = CONTEXT_INFO()
set CONTEXT_INFO @CHANGEAGENTID;
/* Delete the records for volunteers that are in the ITINERARYSTAFFRESOURCEASSIGNMENT table */
delete from dbo.ITINERARYSTAFFRESOURCEASSIGNMENT
where exists (select ITINERARYSTAFFRESOURCE.ID
from dbo.ITINERARYSTAFFRESOURCE
inner join inserted
on inserted.ITINERARYID = ITINERARYSTAFFRESOURCE.ITINERARYID
where (ITINERARYSTAFFRESOURCE.FILLEDBYCODE = 0) and
(ITINERARYSTAFFRESOURCE.ID = ITINERARYSTAFFRESOURCEASSIGNMENT.ITINERARYSTAFFRESOURCEID))
if @CONTEXTCACHE is not null begin
set CONTEXT_INFO @CONTEXTCACHE
end
end;
end
end
|
Trigger Definition
