CREATE trigger dbo.TR_ACCOUNTINGELEMENTRELATIONSHIPDETAIL_INSUPD
on dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL
for insert, update
as
if
(
(substring(columns_updated(),1,1) & 222 > 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
begin try
declare @count int;
select @count = count(DATAELEMENTID) 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
where DATAELEMENTID is not null
group by ID;
if @count is null or @count <= 1
raiserror('ERR_ACCOUNTINGELEMENTRELATIONSHIP_NOELEMENTSSELECTED',13,1);
declare @RULEID nvarchar(4000)
declare @ERRMSG nvarchar(4000)
select @RULEID = coalesce(@RULEID + ', ', '') + AER.RELATIONSHIPID
from inserted i
inner join dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD on
(AERD.DATAELEMENT1ID is null or AERD.DATAELEMENT1ID = i.DATAELEMENT1ID)
and
(AERD.DATAELEMENT2ID is null or AERD.DATAELEMENT2ID = i.DATAELEMENT2ID)
and
(AERD.DATAELEMENT3ID is null or AERD.DATAELEMENT3ID = i.DATAELEMENT3ID)
and
(AERD.DATAELEMENT4ID is null or AERD.DATAELEMENT4ID = i.DATAELEMENT4ID)
and
(AERD.DATAELEMENT5ID is null or AERD.DATAELEMENT5ID = i.DATAELEMENT5ID)
and
(AERD.DATAELEMENT6ID is null or AERD.DATAELEMENT6ID = i.DATAELEMENT6ID)
and
(AERD.DATAELEMENT7ID is null or AERD.DATAELEMENT7ID = i.DATAELEMENT7ID)
and
(AERD.DATAELEMENT8ID is null or AERD.DATAELEMENT8ID = i.DATAELEMENT8ID)
and
(AERD.DATAELEMENT9ID is null or AERD.DATAELEMENT9ID = i.DATAELEMENT9ID)
and
(AERD.DATAELEMENT10ID is null or AERD.DATAELEMENT10ID = i.DATAELEMENT10ID)
and
(AERD.DATAELEMENT11ID is null or AERD.DATAELEMENT11ID = i.DATAELEMENT11ID)
and
(AERD.DATAELEMENT12ID is null or AERD.DATAELEMENT12ID = i.DATAELEMENT12ID)
and
(AERD.DATAELEMENT13ID is null or AERD.DATAELEMENT13ID = i.DATAELEMENT13ID)
and
(AERD.DATAELEMENT14ID is null or AERD.DATAELEMENT14ID = i.DATAELEMENT14ID)
and
(AERD.DATAELEMENT15ID is null or AERD.DATAELEMENT15ID = i.DATAELEMENT15ID)
and
(AERD.DATAELEMENT16ID is null or AERD.DATAELEMENT16ID = i.DATAELEMENT16ID)
and
(AERD.DATAELEMENT17ID is null or AERD.DATAELEMENT17ID = i.DATAELEMENT17ID)
and
(AERD.DATAELEMENT18ID is null or AERD.DATAELEMENT18ID = i.DATAELEMENT18ID)
and
(AERD.DATAELEMENT19ID is null or AERD.DATAELEMENT19ID = i.DATAELEMENT19ID)
and
(AERD.DATAELEMENT20ID is null or AERD.DATAELEMENT20ID = i.DATAELEMENT20ID)
and
(AERD.DATAELEMENT21ID is null or AERD.DATAELEMENT21ID = i.DATAELEMENT21ID)
and
(AERD.DATAELEMENT22ID is null or AERD.DATAELEMENT22ID = i.DATAELEMENT22ID)
and
(AERD.DATAELEMENT23ID is null or AERD.DATAELEMENT23ID = i.DATAELEMENT23ID)
and
(AERD.DATAELEMENT24ID is null or AERD.DATAELEMENT24ID = i.DATAELEMENT24ID)
and
(AERD.DATAELEMENT25ID is null or AERD.DATAELEMENT25ID = i.DATAELEMENT25ID)
and
(AERD.DATAELEMENT26ID is null or AERD.DATAELEMENT26ID = i.DATAELEMENT26ID)
and
(AERD.DATAELEMENT27ID is null or AERD.DATAELEMENT27ID = i.DATAELEMENT27ID)
and
(AERD.DATAELEMENT28ID is null or AERD.DATAELEMENT28ID = i.DATAELEMENT28ID)
and
(AERD.DATAELEMENT29ID is null or AERD.DATAELEMENT29ID = i.DATAELEMENT29ID)
and
(AERD.DATAELEMENT30ID is null or AERD.DATAELEMENT30ID = i.DATAELEMENT30ID)
inner join dbo.ACCOUNTINGELEMENTRELATIONSHIP AER on AERD.ACCOUNTINGELEMENTRELATIONSHIPID = AER.ID
where AERD.RESTRICTIONCODE <> i.RESTRICTIONCODE
group by AER.ID, AER.RELATIONSHIPID
if @RULEID is not null
begin
set @ERRMSG = N'Saving this relationship will result in a conflict with relationship(s) '+@RULEID+'.'
raiserror(@ERRMSG,13,1)
end
if exists (select 1
from dbo.GLACCOUNT LA
inner join inserted 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)
--this only applies if NOT restricting an account.
where AERD.ACCOUNTID is null
)
raiserror('ERR_ACCOUNTINGELEMENTRELATIONSHIP_EXISTINGACCOUNT',13,1)
if exists (select 1
from dbo.CONTROLACCOUNT CA
inner join inserted 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)
)
raiserror('ERR_ACCOUNTINGELEMENTRELATIONSHIP_EXISTINGCONTROLACCOUNT',13,1)
if exists (select 1
from dbo.BALANCINGENTRYELEMENT BEE
inner join inserted 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)
)
raiserror('ERR_ACCOUNTINGELEMENTRELATIONSHIP_EXISTINGBALANCINGENTRY',13,1)
end try
begin catch
exec dbo.USP_RAISE_ERROR
end catch
end
|
Trigger Definition
