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;
  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)
  )
  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
  )
  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 )
  )
  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
  )
  BEGIN
    RAISERROR ('UIX_BANKACCOUNT_PDACCOUNTSEGMENTVALUEID', 16, 1)
    ROLLBACK
  END
end