CREATE trigger [dbo].[TR_ITINERARYATTENDEE_DELETE_RESOURCE_UPDATE] on [dbo].[ITINERARYATTENDEE] 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(ITINERARYRESOURCE.PERTICKETQUANTITY * deleted.QUANTITY) as qty
from dbo.RESOURCE
inner join dbo.ITINERARYRESOURCE on RESOURCE.ID = ITINERARYRESOURCE.RESOURCEID
inner join deleted on ITINERARYRESOURCE.ITINERARYID = deleted.ITINERARYID
where [RESOURCE].ISPERTICKETITEM <> 0 and [RESOURCE].TYPECODE = 1
group by [RESOURCE].ID) as VT
where [RESOURCE].ID = VT.ID
-- Update per-ticket consumable itinerary item resources
update dbo.RESOURCE
set [QUANTITY] = [RESOURCE].[QUANTITY] + qty,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from (
select [RESOURCE].ID, sum(ITINERARYITEMRESOURCE.PERTICKETQUANTITY * deleted.QUANTITY) as qty
from dbo.RESOURCE
inner join dbo.ITINERARYITEMRESOURCE on RESOURCE.ID = ITINERARYITEMRESOURCE.RESOURCEID
inner join dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
inner join deleted on ITINERARYITEM.ITINERARYID = deleted.ITINERARYID
where dbo.[RESOURCE].ISPERTICKETITEM <> 0 and dbo.[RESOURCE].TYPECODE = 1
group by [RESOURCE].ID) as VT
where [RESOURCE].ID = VT.ID
-- Update per-ticket consumable scheduled event itinerary item resources
update dbo.RESOURCE
set [QUANTITY] = [RESOURCE].[QUANTITY] + qty,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from (
select [RESOURCE].ID, sum(EVENTRESOURCE.PERTICKETQUANTITY * deleted.QUANTITY) as qty
from dbo.RESOURCE
inner join dbo.EVENTRESOURCE on RESOURCE.ID = EVENTRESOURCE.RESOURCEID
inner join dbo.ITINERARYITEM on EVENTRESOURCE.EVENTID = ITINERARYITEM.EVENTID
inner join deleted on ITINERARYITEM.ITINERARYID = deleted.ITINERARYID
where [RESOURCE].ISPERTICKETITEM <> 0 and [RESOURCE].TYPECODE = 1
group by [RESOURCE].ID) as VT
where [RESOURCE].ID = VT.ID
end
|
Trigger Definition
