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
|
Trigger Definition
