TR_FINANCIALTRANSACTIONAPPLICATION_INSERT_UPDATE (Trigger)

        
CREATE trigger TR_FINANCIALTRANSACTIONAPPLICATION_INSERT_UPDATE 
    on dbo.FINANCIALTRANSACTIONAPPLICATION 
        after insert, update 
    not for replication
as 
begin
  set nocount on;    
  
    if exists(
        select 
          *
        from                         
            (select distinct FINANCIALTRANSACTIONSCHEDULEID from 
                INSERTED
            ) as SCHEDULES
            inner join dbo.FINANCIALTRANSACTIONSCHEDULE as FTS on FTS.ID = SCHEDULES.FINANCIALTRANSACTIONSCHEDULEID and FTS.DELETED = 0
      left outer join dbo.FINANCIALTRANSACTIONAPPLICATION as FTA 
        on FTA.FINANCIALTRANSACTIONSCHEDULEID = SCHEDULES.FINANCIALTRANSACTIONSCHEDULEID 
    where FTA.[STATUSCODE] in (0,1)                    
        group by FTS.ID, FTS.AMOUNT
        having
              COALESCE(SUM(FTA.AMOUNT),0 ) > FTS.AMOUNT
    )   
    raiserror('FT_CANNOT_APPLY_MORE_THAN_SCHEDULED' , 16, 1);            

    if exists(
        select I.*
        from INSERTED I
        inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FTS.ID = I.FINANCIALTRANSACTIONSCHEDULEID
        where FTS.DELETED = 1 and I.STATUSCODE != 2
    )
    raiserror('FT_CANNOT_APPLY_TO_DELETED_SCHEDULE', 16, 1);
end;