CREATE trigger dbo.TR_ACCOUNTSTRUCTURE_INSUPD on dbo.ACCOUNTSTRUCTURE
for insert, update not for replication as
if update(SEPARATORCODE) and exists(select 1 from dbo.LEDGERACCOUNT)
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.ACCOUNTSTRUCTURE;
--declare @msg nvarchar(100) = N'Separator: ' + @separator
--raiserror(@msg,13,1);
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
;with [ELEMENTS] as
(
select unpvt.ID, DATAELEMENTID, SHORTID, SEGMENTSEQUENCE
from
(select LEDGERACCOUNT.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.LEDGERACCOUNT) 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.DATAELEMENT on unpvt.DATAELEMENTID = DATAELEMENT.ID
inner join dbo.ACCOUNTSTRUCTURE on DATAELEMENT.GLACCOUNTSTRUCTUREID = ACCOUNTSTRUCTURE.ID
),
--This CTE actually creates the updated account strings for each account
CTE as
(
select
ID,
ACCT.list.value('.','nvarchar(130)') as ACCTSTR
from
dbo.LEDGERACCOUNT T1
cross apply
(
select isnull(SHORTID+@SEPARATOR,'')
from [ELEMENTS]
where T1.ID = [ELEMENTS].ID
order by SEGMENTSEQUENCE
for xml path(''), type
) ACCT (list)
)
update LEDGERACCOUNT set
ACCOUNTSTRING = left(T1.acctstr, len(T1.acctstr)-1),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
CTE T1
where T1.ID = LEDGERACCOUNT.ID
;
end
|
Trigger Definition
