CREATE trigger [dbo].[TR_ITINERARYITEMRESOURCE_INSERT_RESOURCE_UPDATE] on [dbo].[ITINERARYITEMRESOURCE] after insert not for replication
as begin
declare @CURRENTDATE datetime;
set @CURRENTDATE = GetDate();
declare @CHANGEAGENTID uniqueidentifier;
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
-- Update per-event consumable resources
update dbo.RESOURCE
set [QUANTITY] = [RESOURCE].[QUANTITY] - inserted.QUANTITYNEEDED,
CHANGEDBYID = COALESCE(inserted.CHANGEDBYID, @CHANGEAGENTID),
DATECHANGED = @CURRENTDATE
from dbo.RESOURCE
inner join inserted on RESOURCE.ID = inserted.RESOURCEID
where [RESOURCE].ISPERTICKETITEM = 0
and [RESOURCE].TYPECODE = 1
-- Update per-ticket consumable itinerary item resources
update dbo.RESOURCE
set [QUANTITY] = [RESOURCE].[QUANTITY] - qty,
CHANGEDBYID = COALESCE(VT.CHANGEDBYID, @CHANGEAGENTID),
DATECHANGED = @CURRENTDATE
from (
select
[RESOURCE].ID,
sum(dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(inserted.PERTICKETQUANTITY * ITINERARYATTENDEE.QUANTITY, inserted.PERTICKETDIVISOR)) as qty,
inserted.CHANGEDBYID
from dbo.RESOURCE
inner join inserted on RESOURCE.ID = inserted.RESOURCEID
inner join dbo.ITINERARYITEM on inserted.ITINERARYITEMID = ITINERARYITEM.ID
inner join dbo.ITINERARYATTENDEE on ITINERARYITEM.ITINERARYID = ITINERARYATTENDEE.ITINERARYID
where [RESOURCE].ISPERTICKETITEM <> 0 and [RESOURCE].TYPECODE = 1
group by [RESOURCE].ID, inserted.CHANGEDBYID) as VT
where [RESOURCE].ID = VT.ID
end
|
Trigger Definition
