TR_ITINERARYITEMSTAFFRESOURCE_INSERT_JOBOCCURRENCE

                    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