CREATE trigger [dbo].[TR_GLACCOUNT_UPD] ON [dbo].[GLACCOUNT]
with execute as owner
for update
as
begin
set nocount on
declare @IsBasicGL bit
declare @ChangeAgentID uniqueidentifier
declare @AcctSysID uniqueidentifier
declare @AccountNumber nvarchar(100);
declare @AccountDescription nvarchar(400);
declare @CURRENTDATE datetime = getdate();
declare @ACCOUNTALIAS nvarchar(100);
if (select count(*) from deleted) > 0 and (select count(*) from inserted) = 0
select top 1 @IsBasicGL = ISBASICGL from deleted t1 join PDACCOUNTSYSTEM t2 on t1.PDACCOUNTSYSTEMID = t2.ID
else
select top 1 @IsBasicGL = ISBASICGL from inserted t1 join PDACCOUNTSYSTEM t2 on t1.PDACCOUNTSYSTEMID = t2.ID
select top 1 @ChangeAgentID = ADDEDBYID, @AcctSysID=PDACCOUNTSYSTEMID, @AccountNumber = ACCOUNTNUMBER, @AccountDescription = ACCOUNTDESCRIPTION, @ACCOUNTALIAS = ACCOUNTALIAS from inserted order by DATEADDED
if @IsBasicGL = 1
begin
if update(ACCOUNTNUMBER)
begin
if exists (select t1.* from inserted t1 inner join deleted t2 on t1.ID = t2.ID where t1.ACCOUNTNUMBER != t2.ACCOUNTNUMBER)
begin
if dbo.UFN_GLACCOUNT_VERIFYACCOUNTNUMBER2(@AccountNumber,@AcctSysID) = 0
BEGIN
RAISERROR ('CK_GLACCOUNT_ACCOUNTNUMBERVALID', 16, 1)
ROLLBACK
END
if len(@AccountNumber) = 0 or @AccountNumber is null
BEGIN
RAISERROR ('CK_GLACCOUNT_ACCOUNTNUMBER', 16, 1)
ROLLBACK
END
if len(@AccountDescription) = 0 or @AccountDescription is null
BEGIN
RAISERROR ('CK_GLACCOUNT_ACCOUNTDESCRIPTION', 16, 1)
ROLLBACK
END
if exists(select COUNT(ID) from GLACCOUNT where len(ACCOUNTNUMBER) > 0 group by PDACCOUNTSYSTEMID,ACCOUNTNUMBER having COUNT(ID) > 1)
BEGIN
RAISERROR ('UIX_GLACCOUNT_PDACCOUNTSYSTEMID_ACCOUNTNUMBER', 16, 1)
ROLLBACK
END
if (len(@ACCOUNTALIAS) > 100)
BEGIN
RAISERROR ('CK_GLACCOUNT_ACCOUNTALIASLENGTH', 16, 1)
ROLLBACK
END
insert into dbo.PDACCOUNTSEGMENTVALUE (PDACCOUNTSTRUCTUREID, SHORTDESCRIPTION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select distinct ID, SegValue, @ChangeAgentID, @ChangeAgentID, getdate() AS DATEADDED, getdate() AS DATECHANGED
from
(select t1.ID,
case t1.SEQUENCE
when 1 then t2.SEG1VALUE
when 2 then t2.SEG2VALUE
when 3 then t2.SEG3VALUE
when 4 then t2.SEG4VALUE
when 5 then t2.SEG5VALUE
when 6 then t2.SEG6VALUE
when 7 then t2.SEG7VALUE
when 8 then t2.SEG8VALUE
when 9 then t2.SEG9VALUE
when 10 then t2.SEG10VALUE
when 11 then t2.SEG11VALUE
when 12 then t2.SEG12VALUE
when 13 then t2.SEG13VALUE
when 14 then t2.SEG14VALUE
when 15 then t2.SEG15VALUE
when 16 then t2.SEG16VALUE
when 17 then t2.SEG17VALUE
when 18 then t2.SEG18VALUE
when 19 then t2.SEG19VALUE
when 20 then t2.SEG20VALUE
when 21 then t2.SEG21VALUE
when 22 then t2.SEG22VALUE
when 23 then t2.SEG23VALUE
when 24 then t2.SEG24VALUE
when 25 then t2.SEG25VALUE
when 26 then t2.SEG26VALUE
when 27 then t2.SEG27VALUE
when 28 then t2.SEG28VALUE
when 29 then t2.SEG29VALUE
when 30 then t2.SEG30VALUE
end as SegValue
from (select * from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID = @AcctSysID) t1 cross join inserted t2 ) V1
where not exists (select * from dbo.PDACCOUNTSEGMENTVALUE where PDACCOUNTSTRUCTUREID = V1.ID and SHORTDESCRIPTION = V1.SegValue)
update dbo.PDACCOUNTSEGMENTVALUE set TYPECODE = 1 where PDACCOUNTSTRUCTUREID in (select T1.ID from PDACCOUNTSTRUCTURE T1 join inserted T2 on T1.PDACCOUNTSYSTEMID = T2.PDACCOUNTSYSTEMID where SEGMENTTYPE = 1)
merge dbo.PDACCOUNTSEGMENT as Target
using (select V1.GLACCOUNTID, V1.PDACCOUNTSTRUCTUREID, t3.ID, @ChangeAgentID AS ADDEDBYID, @ChangeAgentID AS CHANGEDBYID, getdate() as DATEADDED, getdate() as DATECHANGED
from
(select t1.ID as PDACCOUNTSTRUCTUREID, t2.ID as GLACCOUNTID,
case t1.SEQUENCE
when 1 then t2.SEG1VALUE
when 2 then t2.SEG2VALUE
when 3 then t2.SEG3VALUE
when 4 then t2.SEG4VALUE
when 5 then t2.SEG5VALUE
when 6 then t2.SEG6VALUE
when 7 then t2.SEG7VALUE
when 8 then t2.SEG8VALUE
when 9 then t2.SEG9VALUE
when 10 then t2.SEG10VALUE
when 11 then t2.SEG11VALUE
when 12 then t2.SEG12VALUE
when 13 then t2.SEG13VALUE
when 14 then t2.SEG14VALUE
when 15 then t2.SEG15VALUE
when 16 then t2.SEG16VALUE
when 17 then t2.SEG17VALUE
when 18 then t2.SEG18VALUE
when 19 then t2.SEG19VALUE
when 20 then t2.SEG20VALUE
when 21 then t2.SEG21VALUE
when 22 then t2.SEG22VALUE
when 23 then t2.SEG23VALUE
when 24 then t2.SEG24VALUE
when 25 then t2.SEG25VALUE
when 26 then t2.SEG26VALUE
when 27 then t2.SEG27VALUE
when 28 then t2.SEG28VALUE
when 29 then t2.SEG29VALUE
when 30 then t2.SEG30VALUE
end as SegValue
from (select * from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID = @AcctSysID) t1 cross join inserted t2 ) V1
inner join dbo.PDACCOUNTSEGMENTVALUE t3 on V1.SegValue = t3.SHORTDESCRIPTION and V1.PDACCOUNTSTRUCTUREID = t3.PDACCOUNTSTRUCTUREID) as Source
on (Target.PDACCOUNTSTRUCTUREID = Source.PDACCOUNTSTRUCTUREID and Target.GLACCOUNTID = Source.GLACCOUNTID)
when matched then
update set Target.PDACCOUNTSEGMENTVALUEID = Source.ID,
Target.CHANGEDBYID = Source.CHANGEDBYID,
Target.DATECHANGED = Source.DATECHANGED
when not matched by Target then
insert (GLACCOUNTID, PDACCOUNTSTRUCTUREID, PDACCOUNTSEGMENTVALUEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (Source.GLACCOUNTID, Source.PDACCOUNTSTRUCTUREID, Source.ID, Source.ADDEDBYID, Source.CHANGEDBYID, Source.DATEADDED, Source.DATECHANGED);
declare @GLACCOUNTID uniqueidentifier;
select top 1 @GLACCOUNTID = ID from inserted order by dateadded desc;
declare @SQL nvarchar(4000)
select @SQL = (select 'DATAELEMENT' + cast(T2.SEQUENCE as nvarchar(2))+ 'ID =' + '''' + convert(varchar(36),T1.PDACCOUNTSEGMENTVALUEID) + '''' + ','
from dbo.PDACCOUNTSEGMENT T1
join dbo.PDACCOUNTSTRUCTURE T2 on T1.PDACCOUNTSTRUCTUREID = T2.ID
where T1.GLACCOUNTID = @GLACCOUNTID
order by T2.SEQUENCE for XML PATH(''))
set @SQL = @SQL + 'CHANGEDBYID = ' + '''' + convert(varchar(36), @CHANGEAGENTID) + '''' + ', DATECHANGED = ' + '''' + convert(varchar(36),@CURRENTDATE,110) + '''' + ' where GLACCOUNT.ID = ' + '''' + convert(varchar(36),@GLACCOUNTID) + ''''
set @SQL = 'update GLACCOUNT set ' + @SQL
exec (@SQL)
delete t1
from dbo.PDACCOUNTSEGMENTVALUE t1
where not exists (select * from dbo.PDACCOUNTSEGMENT where PDACCOUNTSEGMENTVALUEID = t1.id)
------------------------------------------------------------------------------------------------
-- Hash Function -- Calculates the hash based on the id of each segment value
------------------------------------------------------------------------------------------------
;with account_cte(ID, MD5HASHVALUE)
as
(
select
id,
dbo.UFN_GLACCOUNT_MD5_HASH_SEGMENTS(
(
select
pvt.[1] SEGMENT01ID, pvt.[2] SEGMENT02ID, pvt.[3] SEGMENT03ID, pvt.[4] SEGMENT04ID, pvt.[5] SEGMENT05ID, pvt.[6] SEGMENT06ID, pvt.[7] SEGMENT07ID, pvt.[8] SEGMENT08ID, pvt.[9] SEGMENT09ID, pvt.[10] SEGMENT10ID, pvt.[11] SEGMENT11ID, pvt.[12] SEGMENT12ID, pvt.[13] SEGMENT13ID, pvt.[14] SEGMENT14ID, pvt.[15] SEGMENT15ID, pvt.[16] SEGMENT16ID, pvt.[17] SEGMENT17ID, pvt.[18] SEGMENT18ID, pvt.[19] SEGMENT19ID, pvt.[20] SEGMENT20ID, pvt.[21] SEGMENT21ID, pvt.[22] SEGMENT22ID, pvt.[23] SEGMENT23ID, pvt.[24] SEGMENT24ID, pvt.[25] SEGMENT25ID, pvt.[26] SEGMENT26ID, pvt.[27] SEGMENT27ID, pvt.[28] SEGMENT28ID, pvt.[29] SEGMENT29ID, pvt.[30] SEGMENT30ID
for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64
)) ACCOUNTHASH
from
(
select g.id, cast(s.PDACCOUNTSEGMENTVALUEID as nvarchar(36)) PDACCOUNTSEGMENTVALUEID, ROW_NUMBER() over(partition by g.id order by s.PDACCOUNTSEGMENTVALUEID) segment
from dbo.GLACCOUNT g inner join
dbo.PDACCOUNTSEGMENT s on g.ID = s.GLACCOUNTID inner join
inserted i on i.ID = g.ID
) source
pivot
(
max(PDACCOUNTSEGMENTVALUEID)
for segment in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30])
) pvt
)
update dbo.GLACCOUNT
set MD5HASHVALUE = h.MD5HASHVALUE
from account_cte h inner join
dbo.GLACCOUNT g on g.ID = h.ID
------------------------------------------------------------------------------------------------
if update(ACCOUNTNUMBER)
delete from dbo.PDACCOUNTLOOKUPCACHE where PDACCOUNTSYSTEMID in (select PDACCOUNTSYSTEMID from inserted)
end
end
end
else if (select count(*) from deleted) > 0 or (select count(*) from inserted) > 0
begin
if not ((charindex('*',@AccountDescription) = 0) and (charindex('?',@AccountDescription) = 0))
begin
raiserror('CK_GLACCOUNT_DESCRIPTIONVALIDCHARACTERS', 16, 1)
rollback
end
declare
--@CURRENTDATE datetime = getdate(),
@SEPARATOR nvarchar(1);
if exists(SELECT I.ID FROM INSERTED I WHERE I.ACCOUNTDESCRIPTION IN (SELECT ACCOUNTDESCRIPTION
FROM dbo.GLACCOUNT WHERE GLACCOUNT.PDACCOUNTSYSTEMID = @AcctSysID and GLACCOUNT.ID <> I.ID)
)
BEGIN
RAISERROR ('UIC_LEDGERACCOUNT_DESCRIPTION', 16, 1)
ROLLBACK
END
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
select top 1 @SEPARATOR = SEPARATOR from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID = @AcctSysID;
;with [ELEMENTS] as
(
select unpvt.ID, DATAELEMENTID, SHORTDESCRIPTION, SEGMENTSEQUENCE
from
(select 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 inserted) as 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
),
CTE as
(
select
ID,
ACCT.list.value('.','nvarchar(100)') as ACCTSTR
from
inserted 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),
ACCOUNTNUMBER = left(T1.acctstr, len(T1.acctstr)-1),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
CTE T1
where T1.ID = GLACCOUNT.ID
;
end
end
|
Trigger Definition
