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