TR_BANKACCOUNTAUTHORIZEDSIGNATURE_SIGNATUREUNIQUE

CREATE trigger TR_BANKACCOUNTAUTHORIZEDSIGNATURE_SIGNATUREUNIQUE on dbo.BANKACCOUNTAUTHORIZEDSIGNATURE after insert, update not for replication
  as begin
    if exists(select I.ID
            from INSERTED I         
            left outer join dbo.BANKACCOUNTAUTHORIZEDSIGNATURE B on I.BANKACCOUNTID = B.BANKACCOUNTID and I.ID != B.ID
            left outer join dbo.SIGNATURE S on S.ID = I.SIGNATUREID 
            left outer join dbo.SIGNATURE S1 on S1.ID = B.SIGNATUREID 
            where (B.MANUALSIGNATURENAME = I.MANUALSIGNATURENAME and I.MANUALSIGNATURENAME != '' and B.MANUALSIGNATURENAME != '') 
                or (B.SIGNATUREID = I.SIGNATUREID and I.SIGNATUREID is not null)
                or (S.ID is not null and dbo.UFN_SIGNATURE_USERNAME(S.SIGNERCODE, S.NAME, S.APPUSERID) = B.MANUALSIGNATURENAME and B.MANUALSIGNATURENAME != '')
                or (S1.ID is not null and dbo.UFN_SIGNATURE_USERNAME(S1.SIGNERCODE, S1.NAME, S1.APPUSERID) = I.MANUALSIGNATURENAME and I.MANUALSIGNATURENAME != '')
            )
        begin
            RAISERROR ('This name already exists as an authorized signature for this bank account.',  16, 1)
            ROLLBACK TRANSACTION
        end
  end