CREATE trigger [dbo].[TR_EVENTSTAFFRESOURCE_INSERT_JOB]
on [dbo].[EVENTSTAFFRESOURCE]
after insert, update not for replication
as begin
if (update(EVENTID))
begin
declare @CURRENTDATE datetime;
set @CURRENTDATE = GetDate();
declare @CHANGEAGENTID uniqueidentifier;
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
/* Update the null job fields with the corresponding jobs of any identical resource from the program or peer events */
update EVENTSTAFFRESOURCE
set EVENTSTAFFRESOURCE.JOBID = PROGRAMSTAFFRESOURCE.JOBID,
EVENTSTAFFRESOURCE.CHANGEDBYID = COALESCE(inserted.CHANGEDBYID, @CHANGEAGENTID),
EVENTSTAFFRESOURCE.DATECHANGED = @CURRENTDATE
from inserted
inner join EVENT on inserted.EVENTID = EVENT.ID
inner join PROGRAMSTAFFRESOURCE on EVENT.PROGRAMID = PROGRAMSTAFFRESOURCE.PROGRAMID
where inserted.JOBID is null
and inserted.ID = EVENTSTAFFRESOURCE.ID
and inserted.VOLUNTEERTYPEID = PROGRAMSTAFFRESOURCE.VOLUNTEERTYPEID
and PROGRAMSTAFFRESOURCE.JOBID is not null
update EVENTSTAFFRESOURCE
set EVENTSTAFFRESOURCE.JOBID = PEERRESOURCE.JOBID,
EVENTSTAFFRESOURCE.CHANGEDBYID = COALESCE(inserted.CHANGEDBYID, @CHANGEAGENTID),
EVENTSTAFFRESOURCE.DATECHANGED = @CURRENTDATE
from inserted
inner join EVENTSTAFFRESOURCE on inserted.ID = EVENTSTAFFRESOURCE.ID
inner join EVENT on inserted.EVENTID = EVENT.ID
inner join EVENT PEEREVENT on EVENT.PROGRAMID = PEEREVENT.PROGRAMID
inner join EVENTSTAFFRESOURCE PEERRESOURCE on PEEREVENT.ID = PEERRESOURCE.EVENTID
where inserted.JOBID is null
and EVENTSTAFFRESOURCE.JOBID is null
and inserted.VOLUNTEERTYPEID = PEERRESOURCE.VOLUNTEERTYPEID
and PEERRESOURCE.JOBID is not null
/* Update the null job fields with the corresponding jobs of any existing jobs with the same name as those we would create */
update EVENTSTAFFRESOURCE
set EVENTSTAFFRESOURCE.JOBID = JOB.ID,
EVENTSTAFFRESOURCE.CHANGEDBYID = COALESCE(inserted.CHANGEDBYID, @CHANGEAGENTID),
EVENTSTAFFRESOURCE.DATECHANGED = @CURRENTDATE
from inserted
inner join EVENTSTAFFRESOURCE on inserted.ID = EVENTSTAFFRESOURCE.ID
inner join JOB on inserted.VOLUNTEERTYPEID = JOB.VOLUNTEERTYPEID
inner join dbo.EVENT on inserted.EVENTID = EVENT.ID
inner join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
inner join dbo.VOLUNTEERTYPE on inserted.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
where inserted.JOBID is null
and EVENTSTAFFRESOURCE.JOBID is null
and JOB.NAME = left(PROGRAM.NAME + ' - ' + VOLUNTEERTYPE.NAME, 100)
/* Create any new jobs requred */
declare @JOBMAP TABLE
(
EVENTSTAFFRESOURCEID uniqueidentifier,
JOBID uniqueidentifier,
CHANGEAGENTID uniqueidentifier
)
insert into @JOBMAP (EVENTSTAFFRESOURCEID, JOBID, CHANGEAGENTID)
select EVENTSTAFFRESOURCE.ID, newid(), inserted.CHANGEDBYID
from inserted
inner join EVENTSTAFFRESOURCE on inserted.ID = EVENTSTAFFRESOURCE.ID
where (EVENTSTAFFRESOURCE.JOBID is null) and (EVENTSTAFFRESOURCE.FILLEDBYCODE = 0) and (not EVENTSTAFFRESOURCE.EVENTID is null)
insert into dbo.JOB (ID, NAME, VOLUNTEERTYPEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
JM.JOBID,
left(PROGRAM.NAME + ' - ' + VOLUNTEERTYPE.NAME, 100) as NAME,
inserted.VOLUNTEERTYPEID,
COALESCE(inserted.CHANGEDBYID, @CHANGEAGENTID) as ADDEDBYID,
COALESCE(inserted.CHANGEDBYID, @CHANGEAGENTID) as CHANGEDBYID,
@CURRENTDATE as DATEADDED,
@CURRENTDATE as DATECHANGED
from inserted
inner join dbo.EVENT on inserted.EVENTID = EVENT.ID
inner join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
inner join dbo.VOLUNTEERTYPE on inserted.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
inner join @JOBMAP JM on inserted.ID = JM.EVENTSTAFFRESOURCEID
update EVENTSTAFFRESOURCE
set EVENTSTAFFRESOURCE.JOBID = JM.JOBID,
EVENTSTAFFRESOURCE.CHANGEDBYID = JM.CHANGEAGENTID,
EVENTSTAFFRESOURCE.DATECHANGED = @CURRENTDATE
from @JOBMAP JM
where JM.EVENTSTAFFRESOURCEID = EVENTSTAFFRESOURCE.ID
/* Add the JobOccurence record, 1 per job per event */
insert into dbo.JOBOCCURRENCE (JOBID, DESCRIPTION, VOLUNTEERSNEEDED,
TYPECODE, STARTDATE, ENDDATE,
STARTMONTHDAY, ENDMONTHDAY, STARTTIME, ENDTIME,
EVENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
EVENTSTAFFRESOURCE.JOBID,
left(VOLUNTEERTYPE.NAME, 30) as DESCRIPTION,
EVENTSTAFFRESOURCE.QUANTITYNEEDED,
0 as TYPECODE,
EVENT.STARTDATE,
EVENT.ENDDATE,
'0000' as STARTMONTHDAY,
'0000' as ENDMONTHDAY,
EVENT.STARTTIME,
EVENT.ENDTIME,
EVENT.ID,
COALESCE(inserted.CHANGEDBYID, @CHANGEAGENTID) as ADDEDBYID,
COALESCE(inserted.CHANGEDBYID, @CHANGEAGENTID) as CHANGEDBYID,
@CURRENTDATE as DATEADDED,
@CURRENTDATE as DATECHANGED
from inserted
inner join dbo.VOLUNTEERTYPE on inserted.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
inner join dbo.EVENTSTAFFRESOURCE on inserted.ID = EVENTSTAFFRESOURCE.ID
inner join dbo.[EVENT] on inserted.EVENTID = [EVENT].ID
left outer join dbo.JOBOCCURRENCE on inserted.EVENTID = JOBOCCURRENCE.EVENTID
and EVENTSTAFFRESOURCE.JOBID = JOBOCCURRENCE.JOBID
where JOBOCCURRENCE.ID is null and EVENTSTAFFRESOURCE.JOBID is not null
/* Update the JobOccurence field to allow us to backtrack later */
update EVENTSTAFFRESOURCE
set EVENTSTAFFRESOURCE.JOBOCCURRENCEID = JOBOCCURRENCE.ID,
EVENTSTAFFRESOURCE.CHANGEDBYID = COALESCE(inserted.CHANGEDBYID, @CHANGEAGENTID),
EVENTSTAFFRESOURCE.DATECHANGED = @CURRENTDATE
from inserted
inner join EVENTSTAFFRESOURCE on inserted.ID = EVENTSTAFFRESOURCE.ID
inner join JOBOCCURRENCE on JOBOCCURRENCE.EVENTID = EVENTSTAFFRESOURCE.EVENTID and JOBOCCURRENCE.JOBID = EVENTSTAFFRESOURCE.JOBID
where EVENTSTAFFRESOURCE.JOBOCCURRENCEID is null
end
end
|
Trigger Definition
