TR_PROGRAMSTAFFRESOURCE_INSERT_JOB

                    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