TR_JOBOCCURRENCE_INSERTUPDATE_CURRENCY (Trigger)

        
create trigger dbo.[TR_JOBOCCURRENCE_INSERTUPDATE_CURRENCY] on dbo.[JOBOCCURRENCE] 
after insert, update 
not for replication
as
begin
  set nocount on;

  -- occurrences inherit their base currency from the parent job

  update dbo.[JOBOCCURRENCE] set
    [JOBOCCURRENCE].[BASECURRENCYID] = [JOB].[BASECURRENCYID],
    [JOBOCCURRENCE].[CHANGEDBYID] = [JOBOCCURRENCE].[CHANGEDBYID],
    [JOBOCCURRENCE].[DATECHANGED] = [JOBOCCURRENCE].[DATECHANGED]
  from inserted
  inner join dbo.[JOBOCCURRENCE] on [JOBOCCURRENCE].[ID] = inserted.[ID]
  inner join dbo.[JOB] on [JOB].[ID] = [JOBOCCURRENCE].[JOBID]
  where [JOBOCCURRENCE].[BASECURRENCYID] is null;

  declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

  if update([ESTIMATEDVALUE])
    update dbo.[JOBOCCURRENCE] set
      [JOBOCCURRENCE].[ORGANIZATIONESTIMATEDVALUE] = [JOBOCCURRENCE].[ESTIMATEDVALUE],

      [JOBOCCURRENCE].[CHANGEDBYID] = [JOBOCCURRENCE].[CHANGEDBYID],
      [JOBOCCURRENCE].[DATECHANGED] = [JOBOCCURRENCE].[DATECHANGED]
    from inserted
    inner join dbo.[JOBOCCURRENCE] on [JOBOCCURRENCE].[ID] = inserted.[ID]
    where [JOBOCCURRENCE].[ORGANIZATIONEXCHANGERATEID] is null
    and [JOBOCCURRENCE].[BASECURRENCYID] = @ORGANIZATIONCURRENCYID
    and ([JOBOCCURRENCE].[ORGANIZATIONESTIMATEDVALUE] <> [JOBOCCURRENCE].[ESTIMATEDVALUE] or [JOBOCCURRENCE].[ESTIMATEDVALUE] = 0);
end