CREATE trigger dbo.TR_LEDGERACCOUNT_INSUPD
on dbo.LEDGERACCOUNT for insert, update
as
if
(
(substring(columns_updated(),1,1) & 224 > 0) or
(substring(columns_updated(),2,1) & 255 > 0) or
(substring(columns_updated(),3,1) & 255 > 0) or
(substring(columns_updated(),4,1) & 255 > 0) or
(substring(columns_updated(),5,1) & 7 > 0)
)
begin
-- Validate that the account doesn't violate any existing element relationships
if exists (select 1 from dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL)
begin
declare @RULEID nvarchar(4000) = null
begin try
select @RULEID = coalesce(@RULEID + ', ', '') + AER.RELATIONSHIPID
from inserted LA
inner join dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD on
(AERD.DATAELEMENT1ID is null or AERD.DATAELEMENT1ID = LA.DATAELEMENT1ID)
and
(AERD.DATAELEMENT2ID is null or AERD.DATAELEMENT2ID = LA.DATAELEMENT2ID)
and
(AERD.DATAELEMENT3ID is null or AERD.DATAELEMENT3ID = LA.DATAELEMENT3ID)
and
(AERD.DATAELEMENT4ID is null or AERD.DATAELEMENT4ID = LA.DATAELEMENT4ID)
and
(AERD.DATAELEMENT5ID is null or AERD.DATAELEMENT5ID = LA.DATAELEMENT5ID)
and
(AERD.DATAELEMENT6ID is null or AERD.DATAELEMENT6ID = LA.DATAELEMENT6ID)
and
(AERD.DATAELEMENT7ID is null or AERD.DATAELEMENT7ID = LA.DATAELEMENT7ID)
and
(AERD.DATAELEMENT8ID is null or AERD.DATAELEMENT8ID = LA.DATAELEMENT8ID)
and
(AERD.DATAELEMENT9ID is null or AERD.DATAELEMENT9ID = LA.DATAELEMENT9ID)
and
(AERD.DATAELEMENT10ID is null or AERD.DATAELEMENT10ID = LA.DATAELEMENT10ID)
and
(AERD.DATAELEMENT11ID is null or AERD.DATAELEMENT11ID = LA.DATAELEMENT11ID)
and
(AERD.DATAELEMENT12ID is null or AERD.DATAELEMENT12ID = LA.DATAELEMENT12ID)
and
(AERD.DATAELEMENT13ID is null or AERD.DATAELEMENT13ID = LA.DATAELEMENT13ID)
and
(AERD.DATAELEMENT14ID is null or AERD.DATAELEMENT14ID = LA.DATAELEMENT14ID)
and
(AERD.DATAELEMENT15ID is null or AERD.DATAELEMENT15ID = LA.DATAELEMENT15ID)
and
(AERD.DATAELEMENT16ID is null or AERD.DATAELEMENT16ID = LA.DATAELEMENT16ID)
and
(AERD.DATAELEMENT17ID is null or AERD.DATAELEMENT17ID = LA.DATAELEMENT17ID)
and
(AERD.DATAELEMENT18ID is null or AERD.DATAELEMENT18ID = LA.DATAELEMENT18ID)
and
(AERD.DATAELEMENT19ID is null or AERD.DATAELEMENT19ID = LA.DATAELEMENT19ID)
and
(AERD.DATAELEMENT20ID is null or AERD.DATAELEMENT20ID = LA.DATAELEMENT20ID)
and
(AERD.DATAELEMENT21ID is null or AERD.DATAELEMENT21ID = LA.DATAELEMENT21ID)
and
(AERD.DATAELEMENT22ID is null or AERD.DATAELEMENT22ID = LA.DATAELEMENT22ID)
and
(AERD.DATAELEMENT23ID is null or AERD.DATAELEMENT23ID = LA.DATAELEMENT23ID)
and
(AERD.DATAELEMENT24ID is null or AERD.DATAELEMENT24ID = LA.DATAELEMENT24ID)
and
(AERD.DATAELEMENT25ID is null or AERD.DATAELEMENT25ID = LA.DATAELEMENT25ID)
and
(AERD.DATAELEMENT26ID is null or AERD.DATAELEMENT26ID = LA.DATAELEMENT26ID)
and
(AERD.DATAELEMENT27ID is null or AERD.DATAELEMENT27ID = LA.DATAELEMENT27ID)
and
(AERD.DATAELEMENT28ID is null or AERD.DATAELEMENT28ID = LA.DATAELEMENT28ID)
and
(AERD.DATAELEMENT29ID is null or AERD.DATAELEMENT29ID = LA.DATAELEMENT29ID)
and
(AERD.DATAELEMENT30ID is null or AERD.DATAELEMENT30ID = LA.DATAELEMENT30ID)
inner join dbo.ACCOUNTINGELEMENTRELATIONSHIP AER on AERD.ACCOUNTINGELEMENTRELATIONSHIPID = AER.ID
group by AER.ID, RELATIONSHIPID;
declare @ERRMSG nvarchar(4000) = N'This account is in conflict with relationship(s) '+@RULEID+' and therefore cannot be saved.'
if @RULEID is not null
raiserror(@ERRMSG,13,1)
--raiserror('ERR_LEDGERACCOUNT_INVALIDRELATIONSHIP',13,1)
end try
begin catch
exec dbo.USP_RAISE_ERROR
end catch
end
declare
@CHANGEAGENTID uniqueidentifier,
@CURRENTDATE datetime = getdate(),
@SEPARATOR nvarchar(1);
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
select top 1 @SEPARATOR = SEPARATOR from dbo.ACCOUNTSTRUCTURE;
;with [ELEMENTS] as
(
select unpvt.ID, DATAELEMENTID, SHORTID, 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.DATAELEMENT on unpvt.DATAELEMENTID = DATAELEMENT.ID
inner join dbo.ACCOUNTSTRUCTURE on DATAELEMENT.GLACCOUNTSTRUCTUREID = ACCOUNTSTRUCTURE.ID
),
CTE as
(
select
ID,
ACCT.list.value('.','nvarchar(130)') as ACCTSTR
from
inserted 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
