CREATE trigger [dbo].[TR_ITINERARYITEMSTAFFRESOURCE_INSERT_JOBOCCURRENCE]
on [dbo].[ITINERARYITEMSTAFFRESOURCE]
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. */
declare @JOBMAP TABLE
(
ITINERARYITEMSTAFFRESOURCEID uniqueidentifier,
JOBOCCURRENCEID uniqueidentifier,
CHANGEAGENTID uniqueidentifier
)
insert into @JOBMAP
(ITINERARYITEMSTAFFRESOURCEID, JOBOCCURRENCEID, CHANGEAGENTID)
select ID, newid(), inserted.CHANGEDBYID
from inserted
where (inserted.JOBOCCURRENCEID is null) and
(inserted.FILLEDBYCODE = 0) and
(not inserted.JOBID is null)
/* 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,
NEWITINERARYITEMSTAFFRESOURCE.JOBID,
left(JOB.NAME, 30),
NEWITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED,
0 as TYPECODE,
ITINERARYITEM.STARTDATE,
ITINERARYITEM.ENDDATE,
'0000' as STARTMONTHDAY,
'0000' as ENDMONTHDAY,
ITINERARYITEM.STARTTIME,
ITINERARYITEM.ENDTIME,
NEWITINERARYITEMSTAFFRESOURCE.ADDEDBYID,
NEWITINERARYITEMSTAFFRESOURCE.CHANGEDBYID,
@CURRENTDATE as DATEADDED,
@CURRENTDATE as DATECHANGED
from inserted as NEWITINERARYITEMSTAFFRESOURCE
inner join dbo.JOB
on NEWITINERARYITEMSTAFFRESOURCE.JOBID = JOB.ID
inner join dbo.ITINERARYITEM
on ITINERARYITEM.ID = NEWITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID
inner join @JOBMAP as JOBMAP
on JOBMAP.ITINERARYITEMSTAFFRESOURCEID = NEWITINERARYITEMSTAFFRESOURCE.ID
/* Now update the ITINERARYITEMSTAFFRESOURCE table with the correct JOBOCCURRENCEIDs */
update dbo.ITINERARYITEMSTAFFRESOURCE
set ITINERARYITEMSTAFFRESOURCE.JOBOCCURRENCEID = JOBMAP.JOBOCCURRENCEID,
ITINERARYITEMSTAFFRESOURCE.CHANGEDBYID = JOBMAP.CHANGEAGENTID,
ITINERARYITEMSTAFFRESOURCE.DATECHANGED = @CURRENTDATE
from dbo.ITINERARYITEMSTAFFRESOURCE
inner join @JOBMAP as JOBMAP
on JOBMAP.ITINERARYITEMSTAFFRESOURCEID = ITINERARYITEMSTAFFRESOURCE.ID
end
|
Trigger Definition
