TR_BANKACCOUNT_ACCOUNTCODE_ISUNIQUE

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;
  DECLARE @type CHAR(1);-- 'U' for update, 'I' for insert

  IF EXISTS(SELECT * FROM inserted)
   BEGIN
    IF EXISTS(SELECT * FROM deleted)
     BEGIN
      SET @type ='U';
     END
    ELSE
     BEGIN
      SET @type ='I';
     END
   END
    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 = CASE @type WHEN 'U' THEN I.STATUSCODE WHEN 'I' THEN 1 END  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 = CASE @type WHEN 'U' THEN I.STATUSCODE WHEN 'I' THEN 1 END
  )
  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 = CASE @type WHEN 'U' THEN I.STATUSCODE WHEN 'I' THEN 1 END
  )
  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 = CASE @type WHEN 'U' THEN I.STATUSCODE WHEN 'I' THEN 1 END
  )
  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 = CASE @type WHEN 'U' THEN I.STATUSCODE WHEN 'I' THEN 1 END
  )
  BEGIN
    RAISERROR ('UIX_BANKACCOUNT_PDACCOUNTSEGMENTVALUEID', 16, 1)
    ROLLBACK
  END
end