TR_ITINERARYSTAFFRESOURCE_INSERT_JOBOCCURRENCE

                    CREATE trigger [dbo].[TR_ITINERARYSTAFFRESOURCE_INSERT_JOBOCCURRENCE] 
                        on [dbo].[ITINERARYSTAFFRESOURCE] 
                        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.
                            Make sure that we only create a mapping for itineraries where we will add a job occurrence. */
                        declare @JOBMAP TABLE
                        (
                            ITINERARYSTAFFRESOURCEID uniqueidentifier,
                            JOBOCCURRENCEID uniqueidentifier,
                            CHANGEAGENTID uniqueidentifier
                        )

                        insert into @JOBMAP
                            (ITINERARYSTAFFRESOURCEID, JOBOCCURRENCEID, CHANGEAGENTID)
                        select NEWITINERARYSTAFFRESOURCE.ID, newid(), NEWITINERARYSTAFFRESOURCE.CHANGEDBYID
                        from inserted as NEWITINERARYSTAFFRESOURCE
                            inner join dbo.ITINERARY
                                on (ITINERARY.ID = NEWITINERARYSTAFFRESOURCE.ITINERARYID) and 
                                    (not ITINERARY.STARTDATETIME is null) and 
                                    (not ITINERARY.ENDDATETIME is null)                        
                        where (NEWITINERARYSTAFFRESOURCE.JOBOCCURRENCEID is null) and
                                (NEWITINERARYSTAFFRESOURCE.FILLEDBYCODE = 0)

                        /* 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,
                            NEWITINERARYSTAFFRESOURCE.JOBID,
                            left(JOB.NAME, 30),
                            NEWITINERARYSTAFFRESOURCE.QUANTITYNEEDED,
                            0 as TYPECODE,
                            dbo.UFN_DATE_GETEARLIESTTIME(ITINERARY.STARTDATETIME),
                            dbo.UFN_DATE_GETEARLIESTTIME(ITINERARY.ENDDATETIME),
                            '0000' as STARTMONTHDAY,
                            '0000' as ENDMONTHDAY,
                            dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.STARTDATETIME),
                            dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.ENDDATETIME),
                            NEWITINERARYSTAFFRESOURCE.ADDEDBYID,
                            NEWITINERARYSTAFFRESOURCE.CHANGEDBYID,
                            @CURRENTDATE as DATEADDED,
                            @CURRENTDATE as DATECHANGED
                        from inserted as NEWITINERARYSTAFFRESOURCE
                            inner join dbo.JOB
                                on NEWITINERARYSTAFFRESOURCE.JOBID = JOB.ID
                            inner join dbo.ITINERARY
                                on (ITINERARY.ID = NEWITINERARYSTAFFRESOURCE.ITINERARYID) and 
                                    (not ITINERARY.STARTDATETIME is null) and 
                                    (not ITINERARY.ENDDATETIME is null)
                            inner join @JOBMAP as JOBMAP
                                on JOBMAP.ITINERARYSTAFFRESOURCEID = NEWITINERARYSTAFFRESOURCE.ID

                        /* Now update the ITINERARYSTAFFRESOURCE table with the correct JOBOCCURRENCEIDs */
                        update dbo.ITINERARYSTAFFRESOURCE
                            set ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID = JOBMAP.JOBOCCURRENCEID,
                                ITINERARYSTAFFRESOURCE.CHANGEDBYID = JOBMAP.CHANGEAGENTID,
                                ITINERARYSTAFFRESOURCE.DATECHANGED = @CURRENTDATE
                        from dbo.ITINERARYSTAFFRESOURCE
                            inner join @JOBMAP as JOBMAP
                                on JOBMAP.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID

                    end