CREATE trigger [dbo].[TR_ITINERARYSTAFFRESOURCE_INSERT_JOBOCCURRENCE]
on [dbo].[ITINERARYSTAFFRESOURCE]
after 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 ITINERARYITEMSTAFFRESOURCEIDs
so we can set the JOBOCCURRENCEID on the ITINERARYITEMSTAFFRESOURCE.
Make sure that we only create a mapping for itineraries where we will add a job occurrence. */
declare @JOBMAP TABLE
(
ITINERARYSTAFFRESOURCEID uniqueidentifier,
JOBOCCURRENCEID uniqueidentifier,
CHANGEAGENTID uniqueidentifier
)
insert into @JOBMAP
(ITINERARYSTAFFRESOURCEID, JOBOCCURRENCEID, CHANGEAGENTID)
select NEWITINERARYSTAFFRESOURCE.ID, newid(), NEWITINERARYSTAFFRESOURCE.CHANGEDBYID
from inserted as NEWITINERARYSTAFFRESOURCE
inner join dbo.ITINERARY
on (ITINERARY.ID = NEWITINERARYSTAFFRESOURCE.ITINERARYID) and
(not ITINERARY.STARTDATETIME is null) and
(not ITINERARY.ENDDATETIME is null)
where (NEWITINERARYSTAFFRESOURCE.JOBOCCURRENCEID is null) and
(NEWITINERARYSTAFFRESOURCE.FILLEDBYCODE = 0)
/* 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,
NEWITINERARYSTAFFRESOURCE.JOBID,
left(JOB.NAME, 30),
NEWITINERARYSTAFFRESOURCE.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),
NEWITINERARYSTAFFRESOURCE.ADDEDBYID,
NEWITINERARYSTAFFRESOURCE.CHANGEDBYID,
@CURRENTDATE as DATEADDED,
@CURRENTDATE as DATECHANGED
from inserted as NEWITINERARYSTAFFRESOURCE
inner join dbo.JOB
on NEWITINERARYSTAFFRESOURCE.JOBID = JOB.ID
inner join dbo.ITINERARY
on (ITINERARY.ID = NEWITINERARYSTAFFRESOURCE.ITINERARYID) and
(not ITINERARY.STARTDATETIME is null) and
(not ITINERARY.ENDDATETIME is null)
inner join @JOBMAP as JOBMAP
on JOBMAP.ITINERARYSTAFFRESOURCEID = NEWITINERARYSTAFFRESOURCE.ID
/* Now update the ITINERARYSTAFFRESOURCE table with the correct JOBOCCURRENCEIDs */
update dbo.ITINERARYSTAFFRESOURCE
set ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID = JOBMAP.JOBOCCURRENCEID,
ITINERARYSTAFFRESOURCE.CHANGEDBYID = JOBMAP.CHANGEAGENTID,
ITINERARYSTAFFRESOURCE.DATECHANGED = @CURRENTDATE
from dbo.ITINERARYSTAFFRESOURCE
inner join @JOBMAP as JOBMAP
on JOBMAP.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID
end
|
Trigger Definition
