CREATE trigger [dbo].[TR_ITINERARYITEM_UPDATE_ITINERARYITEMSTAFFRESOURCEUPDATE]
on [dbo].[ITINERARYITEM]
after update not for replication
as begin
/* This trigger merely makes sure that chagnes to the start or end date or time
update the correct job occurences for this job. */
/* We need some data for the inserts that are going to follow. */
declare @CURRENTDATE datetime;
set @CURRENTDATE = GetDate();
update dbo.JOBOCCURRENCE
set STARTDATE = inserted.STARTDATE,
ENDDATE = inserted.ENDDATE,
STARTTIME = inserted.STARTTIME,
ENDTIME = inserted.ENDTIME,
CHANGEDBYID = inserted.CHANGEDBYID,
DATECHANGED = @CURRENTDATE
from dbo.JOBOCCURRENCE
inner join dbo.ITINERARYITEMSTAFFRESOURCE
on JOBOCCURRENCE.ID = ITINERARYITEMSTAFFRESOURCE.JOBOCCURRENCEID
inner join inserted
on ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = inserted.id
inner join deleted
on inserted.id = deleted.id
where (inserted.STARTDATE <> deleted.STARTDATE) or
(inserted.ENDDATE <> deleted.ENDDATE) or
(inserted.STARTTIME <> deleted.STARTTIME) or
(inserted.ENDTIME <> deleted.ENDTIME)
end
|
Trigger Definition
