TR_ITINERARYITEMRESOURCE_UPDATE_RESOURCE_UPDATE (Trigger)

                
                    CREATE trigger [dbo].[TR_ITINERARYITEMRESOURCE_UPDATE_RESOURCE_UPDATE] on [dbo].[ITINERARYITEMRESOURCE] after update not for replication
                        as begin                                                    
                            declare @CURRENTDATE datetime;                                        
                            set @CURRENTDATE = GetDate();
                            
                            declare @CHANGEAGENTID uniqueidentifier;
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                            -- Consider that the resourceId for the record may change, in addition to the per-ticket quantity or per-event quantity

                            -- Update per-event consumable resources

                            -- Update per-ticket consumable itinerary resources

                            update dbo.RESOURCE
                                set [QUANTITY] = [RESOURCE].[QUANTITY] - qty,
                                    CHANGEDBYID = COALESCE(inserted.CHANGEDBYID, @CHANGEAGENTID),
                                    DATECHANGED = @CURRENTDATE
                            from (
                                select
                                    COALESCE(inserted.ID, deleted.ID) as RECORDID,
                                    [RESOURCE].ID,
                                    sum(
                                        case [RESOURCE].ISPERTICKETITEM
                                            when 0 then
                                                COALESCE(inserted.QUANTITYNEEDED, 0) - COALESCE(deleted.QUANTITYNEEDED, 0)
                                            else
                                                dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2((COALESCE(inserted.PERTICKETQUANTITY, 0) * COALESCE(insertedITINERARYATTENDEE.QUANTITY, 0)), COALESCE(inserted.PERTICKETDIVISOR, 1))
                                                    - dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2((COALESCE(deleted.PERTICKETQUANTITY, 0) * COALESCE(deletedITINERARYATTENDEE.QUANTITY, 0)), COALESCE(deleted.PERTICKETDIVISOR, 1))
                                        end
                                    ) as qty
                                from dbo.RESOURCE
                                    full outer join inserted on RESOURCE.ID = inserted.RESOURCEID
                                    full outer join deleted on RESOURCE.ID = deleted.RESOURCEID
                                    left join dbo.ITINERARYITEM insertedITINERARYITEM on inserted.ITINERARYITEMID = insertedITINERARYITEM.ID
                                    left join dbo.ITINERARYATTENDEE insertedITINERARYATTENDEE on insertedITINERARYITEM.ITINERARYID = insertedITINERARYATTENDEE.ITINERARYID
                                    left join dbo.ITINERARYITEM deletedITINERARYITEM on deleted.ITINERARYITEMID = deletedITINERARYITEM.ID
                                    left join dbo.ITINERARYATTENDEE deletedITINERARYATTENDEE on deletedITINERARYITEM.ITINERARYID = deletedITINERARYATTENDEE.ITINERARYID
                                where [RESOURCE].TYPECODE = 1
                                    and (inserted.RESOURCEID is not null or deleted.RESOURCEID is not null)
                                group by [RESOURCE].ID, COALESCE(inserted.ID, deleted.ID)) as VT
                            inner join inserted on VT.RECORDID = inserted.ID
                            where [RESOURCE].ID = VT.ID and VT.qty <> 0
                        end