TR_PDACCOUNTSTRUCTURE_UPDDEL

CREATE trigger [dbo].[TR_PDACCOUNTSTRUCTURE_UPDDEL]
on [dbo].[PDACCOUNTSTRUCTURE]
for update,delete
as

set nocount on
declare @IsDelete bit = 0
declare @SysID uniqueidentifier
declare @ChangeAgentID uniqueidentifier
declare @Now datetime = getdate()
declare @UpdateSequence bit = 0
declare @UpdateLength bit = 0
declare @UpdateSeparator bit = 0         
declare @IsBasicGL bit;

if (select count(*) from deleted) > 0 and (select count(*) from inserted) = 0
  select top 1 @IsBasicGL = ISBASICGL from deleted 
else                
  select top 1 @IsBasicGL = ISBASICGL from inserted 

if @IsBasicGL = 1
begin
  if exists(select  COUNT(ID) from PDACCOUNTSTRUCTURE where DESCRIPTION is not null group by PDACCOUNTSYSTEMID,DESCRIPTION having COUNT(ID) > 1
  )
  BEGIN
    RAISERROR ('UIX_PDACCOUNTSTRUCTURE_DESCRIPTION_PDACCOUNTSYSTEMID', 16, 1)
    ROLLBACK
  END

  if (select count(*) from deleted) > 0 and (select count(*) from inserted) = 0
  begin
    set @IsDelete = 1
    select top 1 @ChangeAgentID = CHANGEDBYID, @SysID = PDACCOUNTSYSTEMID from deleted where CHANGEDBYID is not null order by DATECHANGED
  end
  else if (select count(*) from INSERTED) > 0
  begin
    select top 1 @ChangeAgentID = CHANGEDBYID, @SysID = PDACCOUNTSYSTEMID from inserted where CHANGEDBYID is not null order by DATECHANGED
    --Infinity always updates all fields so if update(FIELDNAME) is useless

    if exists (select t1.* from inserted t1 inner join deleted t2 on t1.id = t2.id where t1.SEQUENCE != t2.SEQUENCE)
      set @UpdateSequence = 1
    if exists (select t1.* from inserted t1 inner join deleted t2 on t1.id = t2.id where t1.LENGTH != t2.LENGTH)
      set @UpdateLength = 1
    if exists (select t1.* from inserted t1 inner join deleted t2 on t1.id = t2.id where t1.SEPARATORCODE != t2.SEPARATORCODE)
      set @UpdateSeparator = 1
  end

  if exists (select t1.* from inserted t1 inner join deleted t2 on t1.id = t2.id where t1.LENGTH = t2.LENGTH and t1.LENGTH = 0 )
  BEGIN
    RAISERROR ('CK_PDACCOUNTSTRUCTURE_VALIDMAXMINLENGTH', 16, 1)
    ROLLBACK
  END                      

  if @UpdateSequence = 1 or @IsDelete = 1    --Updating the sequence field or deleting a row

    update dbo.PDACCOUNTSTRUCTURE set
      SEQUENCE = V1.NewSequence,
      CHANGEDBYID = @ChangeAgentID,
      DATECHANGED = @Now
    from dbo.PDACCOUNTSTRUCTURE inner join
      (select t1.ID, row_number() over(partition by t1.SITEID order by t1.SEQUENCE, case when t2.SEQUENCE > t3.SEQUENCE then 99 when t2.SEQUENCE < t3.SEQUENCE then 0 else t1.SEQUENCE end, t1.DATEADDED) as NewSequence
        from dbo.PDACCOUNTSTRUCTURE t1 left join inserted t2 on t1.ID = t2.ID
    left join deleted t3 on t1.ID = t3.ID where T1.PDACCOUNTSYSTEMID = @SysID) V1
    on PDACCOUNTSTRUCTURE.ID = V1.ID

  if @UpdateLength = 1
  begin
    declare @Length int = 0    
    declare @Length2 int = 0    
    select top 1 @Length = LENGTH from inserted  order by DATECHANGED

    if exists (select 1 from dbo.GLACCOUNT where GLACCOUNT.PDACCOUNTSYSTEMID = @SysID)
    begin
      select top 1 @Length2 = LENGTH from deleted  order by DATECHANGED
      If @Length2 > @Length 
        BEGIN
          RAISERROR ('CK_PDACCOUNTSTRUCTURE_NEWLENGTH', 16, 1)
          ROLLBACK
        END
    end

    If not (@Length > 0 and @Length < 101)
    BEGIN
      RAISERROR ('CK_PDACCOUNTSTRUCTURE_VALIDMAXMINLENGTH', 16, 1)
      ROLLBACK
    END

    update t2 set
      SHORTDESCRIPTION = case t1.SEGMENTTYPE when 1 then t2.SHORTDESCRIPTION+replicate('0',t1.LENGTH-len(t2.SHORTDESCRIPTION)) else replicate('0',t1.LENGTH-len(t2.SHORTDESCRIPTION))+t2.SHORTDESCRIPTION end,
      CHANGEDBYID = @ChangeAgentID,
      DATECHANGED = @Now
    from inserted t1 inner join dbo.PDACCOUNTSEGMENTVALUE t2 on t1.ID = t2.PDACCOUNTSTRUCTUREID
  end

  if @UpdateSequence = 1 or @UpdateLength = 1 or @UpdateSeparator = 1 or @IsDelete = 1
    exec dbo.USP_GLACCOUNT_REFRESH2 @SysID, @ChangeAgentID

  if exists( select * 
                from deleted as D 
                left outer join inserted as I 
                  on D.ID = I.ID 
                where 
                  D.PDACCOUNTTABLESAVAILABLEFORSEGMENTID<>I.PDACCOUNTTABLESAVAILABLEFORSEGMENTID 
                  or 
                  I.PDACCOUNTTABLESAVAILABLEFORSEGMENTID is null
           )
  begin
    exec dbo.USP_PDACCOUNTSEGMENTMAPPINGVIEW_RECREATE;
    exec dbo.USP_PDCOMPOSITESEGMENTMAPPINGVIEW_RECREATE;
  end;


  If exists(select 1 from  inserted where inserted.SEGMENTTYPE = 3)
    begin
      if not exists (select 1 from dbo.PDACCOUNTSEGMENTMAPPING T1 join inserted T2 on T1.PDACCOUNTSTRUCTUREID = T2.ID where T1.ISDEFAULT = 1)
      begin
      -- update / delete 

      declare @ID uniqueidentifier = NEWID();
      insert into dbo.PDACCOUNTSEGMENTMAPPING 
        (ID,PDACCOUNTSTRUCTUREID, LONGDESCRIPTIONID, ISDEFAULT, ISCONSTANTVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
      select 
        @ID,I.ID ,@ID, 1, I.ISCONSTANTVALUE, I.ADDEDBYID, I.CHANGEDBYID, I.DATEADDED, I.DATECHANGED from inserted as I
        inner join deleted as D
        on I.ID = D.ID
      where 
        D.PDACCOUNTTABLESAVAILABLEFORSEGMENTID<>I.PDACCOUNTTABLESAVAILABLEFORSEGMENTID;
      end    
  end    

end