CREATE trigger dbo.TR_PDACCOUNTSTRUCTURE_INSUPD on dbo.PDACCOUNTSTRUCTURE
for insert, update not for replication
as
declare @SysID uniqueidentifier
declare @IsBasicGL bit;
select top 1 @SysID=PDACCOUNTSYSTEMID, @IsBasicGL = ISBASICGL from inserted where ADDEDBYID is not null order by DATEADDED
if @IsBasicGL = 0 and update(SEPARATORCODE) and exists(select 1 from dbo.GLACCOUNT)
and exists(select 1 from inserted I inner join deleted D on I.ID = D.ID and I.SEPARATORCODE <> D.SEPARATORCODE)
begin
declare @SEPARATOR nvarchar(1), @CHANGEAGENTID uniqueidentifier, @CURRENTDATE datetime;
select top 1 @SEPARATOR = case SEPARATORCODE
when 1 then '-'
when 2 then ','
when 3 then '/'
when 4 then ';'
when 5 then '.'
else '-' end
from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID=@SysID;
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
;with [ELEMENTS] as
(
select unpvt.ID, DATAELEMENTID, SHORTDESCRIPTION, SEGMENTSEQUENCE
from
(select GLACCOUNT.ID, DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID
from dbo.GLACCOUNT where GLACCOUNT.PDACCOUNTSYSTEMID = @SysID) P
unpivot
(DATAELEMENTID for dColumn in
(DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID)
) as unpvt
inner join dbo.PDACCOUNTSEGMENTVALUE on unpvt.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
),
--This CTE actually creates the updated account strings for each account
CTE as
(
select
ID,
ACCT.list.value('.','nvarchar(100)') as ACCTSTR
from
dbo.GLACCOUNT T1
cross apply
(
select isnull(SHORTDESCRIPTION+@SEPARATOR,'')
from [ELEMENTS]
where T1.ID = [ELEMENTS].ID
order by SEGMENTSEQUENCE
for xml path(''), type
) ACCT (list)
)
update GLACCOUNT set
ACCOUNTSTRING = left(T1.acctstr, len(T1.acctstr)-1),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
CTE T1
where T1.ID = GLACCOUNT.ID and GLACCOUNT.PDACCOUNTSYSTEMID = @SysID
;
end
|
Trigger Definition
