CREATE trigger [dbo].[TR_PROGRAMSTAFFRESOURCE_INSERT_JOB] on [dbo].[PROGRAMSTAFFRESOURCE] after insert not for replication
as 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 events */
update PROGRAMSTAFFRESOURCE
set PROGRAMSTAFFRESOURCE.JOBID = EVENTSTAFFRESOURCE.JOBID,
PROGRAMSTAFFRESOURCE.CHANGEDBYID = COALESCE(inserted.CHANGEDBYID, @CHANGEAGENTID),
PROGRAMSTAFFRESOURCE.DATECHANGED = @CURRENTDATE
from inserted
inner join EVENT on inserted.PROGRAMID = EVENT.PROGRAMID
inner join EVENTSTAFFRESOURCE on EVENT.ID = EVENTSTAFFRESOURCE.EVENTID
where inserted.JOBID is null
and inserted.ID = PROGRAMSTAFFRESOURCE.ID
and inserted.VOLUNTEERTYPEID = EVENTSTAFFRESOURCE.VOLUNTEERTYPEID
and EVENTSTAFFRESOURCE.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 PROGRAMSTAFFRESOURCE
set PROGRAMSTAFFRESOURCE.JOBID = JOB.ID,
PROGRAMSTAFFRESOURCE.CHANGEDBYID = COALESCE(inserted.CHANGEDBYID, @CHANGEAGENTID),
PROGRAMSTAFFRESOURCE.DATECHANGED = @CURRENTDATE
from inserted
inner join PROGRAMSTAFFRESOURCE on inserted.ID = PROGRAMSTAFFRESOURCE.ID
inner join JOB on inserted.VOLUNTEERTYPEID = JOB.VOLUNTEERTYPEID
inner join dbo.PROGRAM on inserted.PROGRAMID = PROGRAM.ID
inner join dbo.VOLUNTEERTYPE on inserted.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
where inserted.JOBID is null
and PROGRAMSTAFFRESOURCE.JOBID is null
and JOB.NAME = left(PROGRAM.NAME + ' - ' + VOLUNTEERTYPE.NAME, 100)
declare @JOBMAP TABLE
(
PROGRAMSTAFFRESOURCEID uniqueidentifier,
JOBID uniqueidentifier,
CHANGEAGENTID uniqueidentifier
)
insert into @JOBMAP (PROGRAMSTAFFRESOURCEID, JOBID, CHANGEAGENTID)
select inserted.ID, newid(), inserted.CHANGEDBYID
from inserted
inner join PROGRAMSTAFFRESOURCE on inserted.ID = PROGRAMSTAFFRESOURCE.ID
where (PROGRAMSTAFFRESOURCE.JOBID is null) and (PROGRAMSTAFFRESOURCE.FILLEDBYCODE = 0)
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.PROGRAM on inserted.PROGRAMID = PROGRAM.ID
inner join dbo.VOLUNTEERTYPE on inserted.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
inner join @JOBMAP JM on inserted.ID = JM.PROGRAMSTAFFRESOURCEID
update PROGRAMSTAFFRESOURCE
set PROGRAMSTAFFRESOURCE.JOBID = JM.JOBID,
PROGRAMSTAFFRESOURCE.CHANGEDBYID = JM.CHANGEAGENTID,
PROGRAMSTAFFRESOURCE.DATECHANGED = @CURRENTDATE
from @JOBMAP JM
where JM.PROGRAMSTAFFRESOURCEID = PROGRAMSTAFFRESOURCE.ID
end
|
Trigger Definition
