TR_DISBURSEMENTPROCESS_BANKACCOUNTSIGNATURESNOTASSIGNED (Trigger)

        
CREATE trigger dbo.TR_DISBURSEMENTPROCESS_BANKACCOUNTSIGNATURESNOTASSIGNED 
  on DISBURSEMENTPROCESS for insert, update not for replication as
begin
  set nocount on;
  if exists
  ( 
    select I.ID
    from inserted I
    left outer join dbo.BANKACCOUNTAUTHORIZEDSIGNATURE B1 
    on I.SIGNATURE1ID = B1.ID
    left outer join dbo.BANKACCOUNTAUTHORIZEDSIGNATURE B2 
    on I.SIGNATURE2ID = B2.ID
    left outer join dbo.DISBURSEMENTPROCESSSIGNATURE S 
    on S.DISBURSEMENTPROCESSID = I.ID
    left outer join dbo.BANKACCOUNTAUTHORIZEDSIGNATURE B3 
    on S.BANKACCOUNTAUTHORIZEDSIGNATUREID = B3.ID
    where 
    (B1.BANKACCOUNTID <> I.BANKACCOUNTID) 
    or (B2.BANKACCOUNTID <> I.BANKACCOUNTID) 
    or (B3.BANKACCOUNTID <> I.BANKACCOUNTID)
  )
  begin
    raiserror ('The bank account cannot be changed if signatures are assigned to a disbursement process.', 16, 1);
    rollback;
  end
end