CREATE trigger dbo.TR_BALANCINGENTRYELEMENT_INSUPD
on dbo.BALANCINGENTRYELEMENT for insert, update
as
if
(
(substring(columns_updated(),1,1) & 126 > 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)
)
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 BEE
inner join dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD on
(AERD.DATAELEMENT1ID is null or AERD.DATAELEMENT1ID = BEE.DATAELEMENT1ID)
and
(AERD.DATAELEMENT2ID is null or AERD.DATAELEMENT2ID = BEE.DATAELEMENT2ID)
and
(AERD.DATAELEMENT3ID is null or AERD.DATAELEMENT3ID = BEE.DATAELEMENT3ID)
and
(AERD.DATAELEMENT4ID is null or AERD.DATAELEMENT4ID = BEE.DATAELEMENT4ID)
and
(AERD.DATAELEMENT5ID is null or AERD.DATAELEMENT5ID = BEE.DATAELEMENT5ID)
and
(AERD.DATAELEMENT6ID is null or AERD.DATAELEMENT6ID = BEE.DATAELEMENT6ID)
and
(AERD.DATAELEMENT7ID is null or AERD.DATAELEMENT7ID = BEE.DATAELEMENT7ID)
and
(AERD.DATAELEMENT8ID is null or AERD.DATAELEMENT8ID = BEE.DATAELEMENT8ID)
and
(AERD.DATAELEMENT9ID is null or AERD.DATAELEMENT9ID = BEE.DATAELEMENT9ID)
and
(AERD.DATAELEMENT10ID is null or AERD.DATAELEMENT10ID = BEE.DATAELEMENT10ID)
and
(AERD.DATAELEMENT11ID is null or AERD.DATAELEMENT11ID = BEE.DATAELEMENT11ID)
and
(AERD.DATAELEMENT12ID is null or AERD.DATAELEMENT12ID = BEE.DATAELEMENT12ID)
and
(AERD.DATAELEMENT13ID is null or AERD.DATAELEMENT13ID = BEE.DATAELEMENT13ID)
and
(AERD.DATAELEMENT14ID is null or AERD.DATAELEMENT14ID = BEE.DATAELEMENT14ID)
and
(AERD.DATAELEMENT15ID is null or AERD.DATAELEMENT15ID = BEE.DATAELEMENT15ID)
and
(AERD.DATAELEMENT16ID is null or AERD.DATAELEMENT16ID = BEE.DATAELEMENT16ID)
and
(AERD.DATAELEMENT17ID is null or AERD.DATAELEMENT17ID = BEE.DATAELEMENT17ID)
and
(AERD.DATAELEMENT18ID is null or AERD.DATAELEMENT18ID = BEE.DATAELEMENT18ID)
and
(AERD.DATAELEMENT19ID is null or AERD.DATAELEMENT19ID = BEE.DATAELEMENT19ID)
and
(AERD.DATAELEMENT20ID is null or AERD.DATAELEMENT20ID = BEE.DATAELEMENT20ID)
and
(AERD.DATAELEMENT21ID is null or AERD.DATAELEMENT21ID = BEE.DATAELEMENT21ID)
and
(AERD.DATAELEMENT22ID is null or AERD.DATAELEMENT22ID = BEE.DATAELEMENT22ID)
and
(AERD.DATAELEMENT23ID is null or AERD.DATAELEMENT23ID = BEE.DATAELEMENT23ID)
and
(AERD.DATAELEMENT24ID is null or AERD.DATAELEMENT24ID = BEE.DATAELEMENT24ID)
and
(AERD.DATAELEMENT25ID is null or AERD.DATAELEMENT25ID = BEE.DATAELEMENT25ID)
and
(AERD.DATAELEMENT26ID is null or AERD.DATAELEMENT26ID = BEE.DATAELEMENT26ID)
and
(AERD.DATAELEMENT27ID is null or AERD.DATAELEMENT27ID = BEE.DATAELEMENT27ID)
and
(AERD.DATAELEMENT28ID is null or AERD.DATAELEMENT28ID = BEE.DATAELEMENT28ID)
and
(AERD.DATAELEMENT29ID is null or AERD.DATAELEMENT29ID = BEE.DATAELEMENT29ID)
and
(AERD.DATAELEMENT30ID is null or AERD.DATAELEMENT30ID = BEE.DATAELEMENT30ID)
inner join dbo.ACCOUNTINGELEMENTRELATIONSHIP AER on AERD.ACCOUNTINGELEMENTRELATIONSHIPID = AER.ID
group by AER.ID, AER.RELATIONSHIPID
declare @ERRMSG nvarchar(4000) = N'This balancing entry is in conflict with relationship(s) '+@RULEID+' and therefore cannot be saved.'
if @RULEID is not null
raiserror(@ERRMSG,13,1)
end try
begin catch
exec dbo.USP_RAISE_ERROR
end catch
end
end
|
Trigger Definition
