CREATE trigger dbo.TR_CONTROLACCOUNT_INSUPD
on dbo.CONTROLACCOUNT 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 CA
inner join dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD on
(AERD.DATAELEMENT1ID is null or AERD.DATAELEMENT1ID = CA.DATAELEMENT1ID)
and
(AERD.DATAELEMENT2ID is null or AERD.DATAELEMENT2ID = CA.DATAELEMENT2ID)
and
(AERD.DATAELEMENT3ID is null or AERD.DATAELEMENT3ID = CA.DATAELEMENT3ID)
and
(AERD.DATAELEMENT4ID is null or AERD.DATAELEMENT4ID = CA.DATAELEMENT4ID)
and
(AERD.DATAELEMENT5ID is null or AERD.DATAELEMENT5ID = CA.DATAELEMENT5ID)
and
(AERD.DATAELEMENT6ID is null or AERD.DATAELEMENT6ID = CA.DATAELEMENT6ID)
and
(AERD.DATAELEMENT7ID is null or AERD.DATAELEMENT7ID = CA.DATAELEMENT7ID)
and
(AERD.DATAELEMENT8ID is null or AERD.DATAELEMENT8ID = CA.DATAELEMENT8ID)
and
(AERD.DATAELEMENT9ID is null or AERD.DATAELEMENT9ID = CA.DATAELEMENT9ID)
and
(AERD.DATAELEMENT10ID is null or AERD.DATAELEMENT10ID = CA.DATAELEMENT10ID)
and
(AERD.DATAELEMENT11ID is null or AERD.DATAELEMENT11ID = CA.DATAELEMENT11ID)
and
(AERD.DATAELEMENT12ID is null or AERD.DATAELEMENT12ID = CA.DATAELEMENT12ID)
and
(AERD.DATAELEMENT13ID is null or AERD.DATAELEMENT13ID = CA.DATAELEMENT13ID)
and
(AERD.DATAELEMENT14ID is null or AERD.DATAELEMENT14ID = CA.DATAELEMENT14ID)
and
(AERD.DATAELEMENT15ID is null or AERD.DATAELEMENT15ID = CA.DATAELEMENT15ID)
and
(AERD.DATAELEMENT16ID is null or AERD.DATAELEMENT16ID = CA.DATAELEMENT16ID)
and
(AERD.DATAELEMENT17ID is null or AERD.DATAELEMENT17ID = CA.DATAELEMENT17ID)
and
(AERD.DATAELEMENT18ID is null or AERD.DATAELEMENT18ID = CA.DATAELEMENT18ID)
and
(AERD.DATAELEMENT19ID is null or AERD.DATAELEMENT19ID = CA.DATAELEMENT19ID)
and
(AERD.DATAELEMENT20ID is null or AERD.DATAELEMENT20ID = CA.DATAELEMENT20ID)
and
(AERD.DATAELEMENT21ID is null or AERD.DATAELEMENT21ID = CA.DATAELEMENT21ID)
and
(AERD.DATAELEMENT22ID is null or AERD.DATAELEMENT22ID = CA.DATAELEMENT22ID)
and
(AERD.DATAELEMENT23ID is null or AERD.DATAELEMENT23ID = CA.DATAELEMENT23ID)
and
(AERD.DATAELEMENT24ID is null or AERD.DATAELEMENT24ID = CA.DATAELEMENT24ID)
and
(AERD.DATAELEMENT25ID is null or AERD.DATAELEMENT25ID = CA.DATAELEMENT25ID)
and
(AERD.DATAELEMENT26ID is null or AERD.DATAELEMENT26ID = CA.DATAELEMENT26ID)
and
(AERD.DATAELEMENT27ID is null or AERD.DATAELEMENT27ID = CA.DATAELEMENT27ID)
and
(AERD.DATAELEMENT28ID is null or AERD.DATAELEMENT28ID = CA.DATAELEMENT28ID)
and
(AERD.DATAELEMENT29ID is null or AERD.DATAELEMENT29ID = CA.DATAELEMENT29ID)
and
(AERD.DATAELEMENT30ID is null or AERD.DATAELEMENT30ID = CA.DATAELEMENT30ID)
inner join dbo.ACCOUNTINGELEMENTRELATIONSHIP AER on AERD.ACCOUNTINGELEMENTRELATIONSHIPID = AER.ID
group by AER.ID, AER.RELATIONSHIPID
declare @ERRMSG nvarchar(4000) = N'This subsidiary 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
end
|
Trigger Definition
