CREATE trigger dbo.TR_PDACCOUNTSEGMENTVALUE_UPD_UPDATEACCOUNTSTRING
on dbo.PDACCOUNTSEGMENTVALUE for update
as
declare @IsBasicGL Bit;
declare @ShortID nvarchar(100);
declare @Description nvarchar(60);
declare @Length tinyint;
select top 1 @IsBasicGL = T2.ISBASICGL, @ShortID = T1.SHORTDESCRIPTION, @Description = T1.DESCRIPTION, @Length = T2.LENGTH from inserted T1 join dbo.PDACCOUNTSTRUCTURE T2 on T1.PDACCOUNTSTRUCTUREID = T2.ID
if @IsBasicGL = 0
begin
if UPDATE(SHORTDESCRIPTION)
begin
declare
@CHANGEAGENTID uniqueidentifier,
@CURRENTDATE datetime = getdate();
if not ((len(@ShortID) = @Length) and (@Length > 0)) and not ((@Length = 0) and (len(@ShortID)>=(0)) and (len(@ShortID) < 101))
BEGIN
RAISERROR ('CK_DATAELEMENT_LENGTH', 16, 1)
ROLLBACK
END
if not ((charindex('*',@ShortID) = 0) and (charindex('%',@ShortID) = 0) and (charindex('-',@ShortID) = 0) and (charindex(',',@ShortID) = 0) and (charindex('/',@ShortID) = 0) and (charindex(';',@ShortID) = 0) and (charindex('.',@ShortID) = 0))
BEGIN
RAISERROR ('CK_DATAELEMENT_SHORTIDVALIDCHARACTERS', 16, 1)
ROLLBACK
END
if @Description is null
BEGIN
RAISERROR ('CK_DATAELEMENT_DESCRIPTION', 16, 1)
ROLLBACK
END
if not ((len(@Description)>(0)) and (len(@Description) < 61))
BEGIN
RAISERROR ('CK_DATAELEMENT_DESCRIPTION', 16, 1)
ROLLBACK
END
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @SEPARATOR nvarchar(1);
select top 1 @SEPARATOR = SEPARATOR from dbo.LEDGERPREFERENCE;
--First CTE limits updated accounts to only those which are using the updated dataelements
with [ACCOUNTS] as
(
select unpvt.ID 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 GLACCOUNT) 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.GLACCOUNT on unpvt.ID = GLACCOUNT.ID
inner join inserted on unpvt.DATAELEMENTID = inserted.ID
)
--This CTE has all elements for the accounts in ACCOUNTS
, [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 GLACCOUNT
inner join [ACCOUNTS] on GLACCOUNT.ID = ACCOUNTS.ID) 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
),
--This CTE actually creates the updated account strings for each account
CTE as
(
select
ID,
ACCT.list.value('.','nvarchar(130)') as ACCTSTR
from
[ACCOUNTS] 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
;
end
if update(DESCRIPTION)
begin
if not ((charindex('*',@Description) = 0) and (charindex('?',@Description) = 0))
begin
raiserror('CK_DATAELEMENT_DESCRIPTIONVALIDCHARACTERS', 16, 1)
rollback
end
end
end
else
begin
if not ((len(@ShortID)>=(0)) and (len(@ShortID) < 101))
BEGIN
RAISERROR ('CK_PDACCOUNTSEGMENTVALUE_SHORTDESCRIPTION', 16, 1)
ROLLBACK
END
end
|
Trigger Definition
