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