TR_RECURRINGGIFTINSTALLMENT_INSERTUPDATE_CURRENCY (Trigger)

        
CREATE trigger [dbo].[TR_RECURRINGGIFTINSTALLMENT_INSERTUPDATE_CURRENCY] on [dbo].[RECURRINGGIFTINSTALLMENT] 
after insert, update 
not for replication
as
begin
  set nocount on;
            
  --Replaces CK_RECURRINGGIFTINSTALLMENT_VALIDDATE

  --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

  if exists(select 1
    from inserted I
    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = I.REVENUEID
    where dateadd(ms, -003, dateadd(d, 1, cast(cast(FT.DATE as date) as datetime))) > dateadd(ms, -003, dateadd(d, 1, cast(cast(I.DATE as date) as datetime))))
  BEGIN
    RAISERROR ('CK_RECURRINGGIFTINSTALLMENT_VALIDDATE', 16, 1)
    ROLLBACK
  END
            
  if update(AMOUNT)
  begin
    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(); 

    update
      dbo.RECURRINGGIFTINSTALLMENT
    set
      RECURRINGGIFTINSTALLMENT.ORGANIZATIONAMOUNT = RECURRINGGIFTINSTALLMENT.AMOUNT,
      RECURRINGGIFTINSTALLMENT.TRANSACTIONAMOUNT = RECURRINGGIFTINSTALLMENT.AMOUNT,
      RECURRINGGIFTINSTALLMENT.BASECURRENCYID = @ORGANIZATIONCURRENCYID,
      RECURRINGGIFTINSTALLMENT.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID,
      RECURRINGGIFTINSTALLMENT.CHANGEDBYID = RECURRINGGIFTINSTALLMENT.CHANGEDBYID,
      RECURRINGGIFTINSTALLMENT.DATECHANGED = RECURRINGGIFTINSTALLMENT.DATECHANGED
    from
      inserted
    inner join
      dbo.RECURRINGGIFTINSTALLMENT on inserted.ID = RECURRINGGIFTINSTALLMENT.ID
    where
      RECURRINGGIFTINSTALLMENT.ORGANIZATIONEXCHANGERATEID is null
      and (RECURRINGGIFTINSTALLMENT.BASECURRENCYID is null or RECURRINGGIFTINSTALLMENT.BASECURRENCYID = @ORGANIZATIONCURRENCYID)
      and RECURRINGGIFTINSTALLMENT.BASEEXCHANGERATEID is null
      and (RECURRINGGIFTINSTALLMENT.TRANSACTIONCURRENCYID is null or RECURRINGGIFTINSTALLMENT.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID)
      and (
        RECURRINGGIFTINSTALLMENT.ORGANIZATIONAMOUNT <> RECURRINGGIFTINSTALLMENT.AMOUNT
        or RECURRINGGIFTINSTALLMENT.TRANSACTIONAMOUNT <> RECURRINGGIFTINSTALLMENT.AMOUNT
        or RECURRINGGIFTINSTALLMENT.AMOUNT = 0
      );
  end
end