TR_BANKACCOUNT_ACCOUNTCODE_ISUNIQUE (Trigger)

        
CREATE trigger dbo.TR_BANKACCOUNT_ACCOUNTCODE_ISUNIQUE on BANKACCOUNT for INSERT, UPDATE not for replication as
begin
  set nocount on;
    
    exec USP_GET_KEY_ACCESS;
    if exists(
        select I.ID
        from INSERTED I
        inner join dbo.BANKACCOUNT BA
            on BA.ID <> I.ID and
            convert(nvarchar(50), DecryptByKey(BA.ACCOUNTNUMBER)) = convert(nvarchar(50), DecryptByKey(I.ACCOUNTNUMBER))
        where BA.BANKID = I.BANKID and I.STATUSCODE = 1 and BA.STATUSCODE = 1
        )
    BEGIN
    RAISERROR ('UIX_BANKACCOUNT_BANKID_ACCOUNTNUMBER', 16, 1)
    ROLLBACK
  END
    
  declare @SEGMENT integer
  select @SEGMENT = SEQUENCE from dbo.PDACCOUNTSTRUCTURE where SEGMENTTYPE = 1
  if exists(
    SELECT I.ID
    FROM INSERTED I
    INNER JOIN dbo.GLACCOUNT A on A.ID = I.GLACCOUNTID
    WHERE dbo.UFN_GLACCOUNT_SEGMENTVALUES2(A.ACCOUNTNUMBER, @SEGMENT,I.PDACCOUNTSYSTEMID) IN 
      (SELECT S.SHORTDESCRIPTION
       FROM dbo.PDACCOUNTSEGMENTVALUE S
       INNER JOIN dbo.BANKACCOUNT B on B.PDACCOUNTSEGMENTVALUEID = S.ID
       and B.PDACCOUNTSYSTEMID = I.PDACCOUNTSYSTEMID
       WHERE B.STATUSCODE = 1)
        and I.STATUSCODE = 1
  )
  BEGIN
    RAISERROR ('UIX_BANKACCOUNT_GLACCOUNTID', 16, 1)
    ROLLBACK
  END
  
  if exists(
    SELECT I.ID
    FROM INSERTED I
    INNER JOIN dbo.BANKACCOUNT B on B.ID <> I.ID and B.GLACCOUNTID = I.GLACCOUNTID
    where B.STATUSCODE = 1 and B.GLACCOUNTID is not null and I.STATUSCODE = 1
  )
  BEGIN
    RAISERROR ('UIX_BANKACCOUNT_GLACCOUNTID', 16, 1)
    ROLLBACK
  END
  
  if exists(
    SELECT I.ID
    FROM INSERTED I
    INNER JOIN dbo.PDACCOUNTSEGMENTVALUE V ON I.PDACCOUNTSEGMENTVALUEID = V.ID
    WHERE V.SHORTDESCRIPTION IN
      (SELECT dbo.UFN_GLACCOUNT_SEGMENTVALUES2(A.ACCOUNTNUMBER, @SEGMENT,I.PDACCOUNTSYSTEMID)
       FROM dbo.GLACCOUNT A
       INNER JOIN dbo.BANKACCOUNT B ON B.GLACCOUNTID = A.ID
       WHERE B.STATUSCODE = 1 and B.PDACCOUNTSYSTEMID = I.PDACCOUNTSYSTEMID )
        and I.STATUSCODE = 1
  )
  BEGIN
    RAISERROR ('UIX_BANKACCOUNT_PDACCOUNTSEGMENTVALUEID', 16, 1)
    ROLLBACK
  END
  
  if exists(
    SELECT I.ID
    FROM INSERTED I
    INNER JOIN dbo.BANKACCOUNT B on B.ID <> I.ID and B.PDACCOUNTSEGMENTVALUEID = I.PDACCOUNTSEGMENTVALUEID
    WHERE B.STATUSCODE = 1 and B.PDACCOUNTSEGMENTVALUEID is not null and I.STATUSCODE = 1
  )
  BEGIN
    RAISERROR ('UIX_BANKACCOUNT_PDACCOUNTSEGMENTVALUEID', 16, 1)
    ROLLBACK
  END
end