TR_FINANCIALTRANSACTION_UPDATE

  CREATE trigger TR_FINANCIALTRANSACTION_UPDATE on dbo.FINANCIALTRANSACTION after update, insert not for replication
        as begin
            set nocount on;
            declare @IsAP bit = 0 

            if exists(select 1 from inserted where TYPECODE > 100)    
                set  @IsAP = 1

  --TR_REVENUE_INSERT_BBNCREVENUEIDMAP

      insert into dbo.BBNCREVENUEIDMAP (REVENUEID)
      select 
        inserted.ID 
      from inserted
        left outer join deleted on inserted.ID = deleted.ID
      where 
        inserted.TYPECODE in (0,1,2,3,4,5,6,7,8) and deleted.ID is null
        and not inserted.ID is null

            if UPDATE(CONSTITUENTID) and (@IsAP = 1)
            begin                      
                -- resets REMITADDRESS on invoices when changing vendor            

                update dbo.INVOICE set
                    REMITADDRESSID = null
                    ,CHANGEDBYID = CASE WHEN UPDATE(CHANGEDBYID) THEN I.CHANGEDBYID ELSE NULL END -- force the invoice trigger to take care

                    ,DATECHANGED = CASE WHEN UPDATE(DATECHANGED) THEN I.DATECHANGED ELSE NULL END -- force the invoice trigger to take care

                from
                    dbo.INVOICE as INV 
                    inner join inserted I on INV.ID = I.ID AND I.TYPECODE=101 -- invoice

                    inner join deleted D on I.ID = D.ID
                where
                    I.CONSTITUENTID<>D.CONSTITUENTID

                -- resets REMITADDRESS on credit memos when changing vendor            

                update dbo.INVOICE set
                    REMITADDRESSID = null
                    ,CHANGEDBYID = CASE WHEN UPDATE(CHANGEDBYID) THEN I.CHANGEDBYID ELSE NULL END -- force the invoice trigger to take care

                    ,DATECHANGED = CASE WHEN UPDATE(DATECHANGED) THEN I.DATECHANGED ELSE NULL END -- force the invoice trigger to take care

                from
                    dbo.INVOICE as INV 
                    inner join inserted I on INV.ID = I.ID AND I.TYPECODE=101 -- invoice

                    inner join deleted D on I.ID = D.ID
                where
                    I.CONSTITUENTID<>D.CONSTITUENTID

            end;

        -- Prevent editing based on post status and payment status

        if (update(CONSTITUENTID) or update(TRANSACTIONAMOUNT)) and (@IsAP = 1)
            and exists 
            (
                select D.ID
                from INSERTED I
                inner join DELETED D ON I.ID = D.ID
                inner join FINANCIALTRANSACTIONLINEITEM D_FTLI on D.ID = D_FTLI.FINANCIALTRANSACTIONID
                WHERE 
                    (D.CONSTITUENTID <> I.CONSTITUENTID or D.TRANSACTIONAMOUNT <> I.TRANSACTIONAMOUNT)
                    and (
                          D_FTLI.POSTSTATUSCODE=2 -- it's posted

                        or dbo.UFN_FINANCIALTRANSACTION_PAID(I.ID) = 1 -- Performance : may consider writing and using a tvf here.

                    )                                                    
              )
            raiserror ('ERR_FINANCIALTRANSACTION_LOCKEDFIELDS', 16, 1);

       -- Prevent changing accounting system       

      if update (PDACCOUNTSYSTEMID) and exists( 
        select I.ID 
        from inserted as I 
        inner join deleted as D on I.ID = D.ID 
              inner join dbo.FINANCIALTRANSACTIONLINEITEM as L on L.FINANCIALTRANSACTIONID = D.ID 
              inner join dbo.JOURNALENTRY as J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID 
            where I.PDACCOUNTSYSTEMID<>D.PDACCOUNTSYSTEMID)
          raiserror ('ERR_FINANCIALTRANSACTION_LOCKEDPDACCOUNTSYSTEM', 13, 1);

          --TR_REVENUE_MARKASCONSTITUENT    

          if update(CONSTITUENTID)
              update dbo.CONSTITUENT
                  set ISCONSTITUENT = 1,
                      CHANGEDBYID = inserted.CHANGEDBYID,
                      DATECHANGED = GetDate()
              from inserted inner join CONSTITUENT on inserted.CONSTITUENTID = CONSTITUENT.ID
              where CONSTITUENT.ISCONSTITUENT = 0
              and inserted.TYPECODE in (0,1,2,3,4,5,6,7,8)    

      --Put FK cascade deletes to FinancialTransaction here     

      if update(DELETEDON)
          begin
      if exists (select 1 from dbo.INSTALLMENT inner join inserted on INSTALLMENT.REVENUEID = inserted.ID where DELETEDON is not null)
        delete INSTALLMENT from dbo.INSTALLMENT inner join inserted on INSTALLMENT.REVENUEID = inserted.ID where DELETEDON is not null           

      if exists (select 1 from dbo.INSTALLMENTSPLITWRITEOFF inner join inserted on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = inserted.ID where DELETEDON is not null)
        delete INSTALLMENTSPLITWRITEOFF from dbo.INSTALLMENTSPLITWRITEOFF inner join inserted on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = inserted.ID where DELETEDON is not null

      if exists (select 1 from dbo.SALESORDERPAYMENT inner join inserted on SALESORDERPAYMENT.PAYMENTID = inserted.ID where inserted.DELETEDON is not null)
        delete SALESORDERPAYMENT from dbo.SALESORDERPAYMENT inner join inserted on SALESORDERPAYMENT.PAYMENTID = inserted.ID where inserted.DELETEDON is not null

      if exists (select 1 from dbo.SALESORDER inner join inserted on SALESORDER.REVENUEID = inserted.ID where inserted.DELETEDON is not null)
        update SALESORDER set REVENUEID = null, CHANGEDBYID = inserted.CHANGEDBYID, DATECHANGED = getdate() from dbo.SALESORDER inner join inserted on SALESORDER.REVENUEID = inserted.ID where inserted.DELETEDON is not null

      if exists (select 1 from dbo.PLANNEDGIFTRECONCILE inner join inserted on PLANNEDGIFTRECONCILE.REVENUEID = inserted.ID where inserted.DELETEDON is not null)
        delete PLANNEDGIFTRECONCILE from dbo.PLANNEDGIFTRECONCILE inner join inserted on PLANNEDGIFTRECONCILE.REVENUEID = inserted.ID where inserted.DELETEDON is not null

      if exists (select 1 from dbo.PLANNEDGIFTREVENUE inner join inserted on PLANNEDGIFTREVENUE.REVENUEID = inserted.ID where inserted.DELETEDON is not null)
        update PLANNEDGIFTREVENUE set REVENUEID = null, CHANGEDBYID = inserted.CHANGEDBYID, DATECHANGED = getdate() from dbo.PLANNEDGIFTREVENUE inner join inserted on PLANNEDGIFTREVENUE.REVENUEID = inserted.ID where inserted.DELETEDON is not null

      if exists (select 1 from dbo.RECURRINGGIFTINSTALLMENT inner join inserted on RECURRINGGIFTINSTALLMENT.REVENUEID = inserted.ID where inserted.DELETEDON is not null)
        delete RECURRINGGIFTINSTALLMENT from dbo.RECURRINGGIFTINSTALLMENT inner join inserted on RECURRINGGIFTINSTALLMENT.REVENUEID = inserted.ID where inserted.DELETEDON is not null

      if exists (select 1 from dbo.RECURRINGGIFTINSTALLMENTPAYMENT inner join inserted on RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID = inserted.ID where inserted.DELETEDON is not null)
        delete RECURRINGGIFTINSTALLMENTPAYMENT from dbo.RECURRINGGIFTINSTALLMENTPAYMENT inner join inserted on RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID = inserted.ID where inserted.DELETEDON is not null

          update FINANCIALTRANSACTIONLINEITEM
          set DELETEDON = inserted.DELETEDON,
          CHANGEDBYID = inserted.CHANGEDBYID,
          DATECHANGED = getdate()
          from dbo.FINANCIALTRANSACTIONLINEITEM inner join inserted on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = inserted.ID
          where inserted.DELETEDON is not null
          and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null

      end
  end