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