CREATE trigger TR_FINANCIALTRANSACTION_LINEITEM_APPLICATIONSVALID
on dbo.FINANCIALTRANSACTIONLINEITEM
for insert, update
not for replication as
begin
set nocount on;
begin try
/* If the line item is of type application and both source and target are not filled out,
or line item is not of type application and source or target are filled in. */
if (exists(
select inserted.ID
from inserted
where ((inserted.TYPECODE = 2) and ((inserted.SOURCELINEITEMID is null) or (inserted.TARGETLINEITEMID is null))) or
((inserted.TYPECODE != 2) and ((not inserted.SOURCELINEITEMID is null) or (not inserted.TARGETLINEITEMID is null)))))
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_APPLICATIONSOURCEORTARGETNOTVALID', 13,1);
/* If line item is of type application and is being marked as posted without the source and target line item being posted. */
if (exists(
select inserted.ID
from inserted
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_SOURCE
on inserted.SOURCELINEITEMID = FINANCIALTRANSACTIONLINEITEM_SOURCE.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_TARGET
on inserted.SOURCELINEITEMID = FINANCIALTRANSACTIONLINEITEM_TARGET.ID
where (inserted.POSTSTATUSCODE = 2) and ((FINANCIALTRANSACTIONLINEITEM_SOURCE.POSTSTATUSCODE != 2) or (FINANCIALTRANSACTIONLINEITEM_TARGET.POSTSTATUSCODE != 2))))
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_APPLICATIONSOURCEORTARGETNOTPOSTED', 13,1);
/* if the amount of the applications is larger than the amount of the source line item */
if (exists(
select FINANCIALTRANSACTIONLINEITEM.ID
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join (select APPLICATIONSTABLE_INNER1.SOURCELINEITEMID,
APPLICATIONSTABLE_INNER1.AMOUNT
from inserted as APPLICATIONSTABLE_INNER1
where (APPLICATIONSTABLE_INNER1.TYPECODE = 2)
union
select EXISTINGAPPLICATIONLINEITEM.SOURCELINEITEMID,
EXISTINGAPPLICATIONLINEITEM.AMOUNT
from dbo.FINANCIALTRANSACTIONLINEITEM as EXISTINGAPPLICATIONLINEITEM
left outer join inserted as APPLICATIONSTABLE_INNER2
on APPLICATIONSTABLE_INNER2.ID = EXISTINGAPPLICATIONLINEITEM.ID
where (APPLICATIONSTABLE_INNER2.ID is null) and
(EXISTINGAPPLICATIONLINEITEM.TYPECODE = 2) and
(EXISTINGAPPLICATIONLINEITEM.DELETEDON is null) and
(EXISTINGAPPLICATIONLINEITEM.SOURCELINEITEMID in (select SOURCELINEITEMID from inserted))) as FINANCIALTRANSACTIONLINEITEM_APPLICATIONAMOUNTS
on FINANCIALTRANSACTIONLINEITEM.ID =FINANCIALTRANSACTIONLINEITEM_APPLICATIONAMOUNTS.SOURCELINEITEMID
group by FINANCIALTRANSACTIONLINEITEM.ID,
FINANCIALTRANSACTIONLINEITEM.AMOUNT
having FINANCIALTRANSACTIONLINEITEM.AMOUNT < sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATIONAMOUNTS.AMOUNT, 0))))
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_APPLICATIONSTOTALVALID', 13,1);
/* if the amount of the applications is larger than the amount of the target line item */
if (exists(
select FINANCIALTRANSACTIONLINEITEM.ID
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join (select APPLICATIONSTABLE_INNER1.TARGETLINEITEMID,
APPLICATIONSTABLE_INNER1.AMOUNT
from inserted as APPLICATIONSTABLE_INNER1
where (APPLICATIONSTABLE_INNER1.TYPECODE = 2)
union
select EXISTINGAPPLICATIONLINEITEM.TARGETLINEITEMID,
EXISTINGAPPLICATIONLINEITEM.AMOUNT
from dbo.FINANCIALTRANSACTIONLINEITEM as EXISTINGAPPLICATIONLINEITEM
left outer join inserted as APPLICATIONSTABLE_INNER2
on APPLICATIONSTABLE_INNER2.ID = EXISTINGAPPLICATIONLINEITEM.ID
where (APPLICATIONSTABLE_INNER2.ID is null) and
(EXISTINGAPPLICATIONLINEITEM.TYPECODE = 2) and
(EXISTINGAPPLICATIONLINEITEM.DELETEDON is null) and
(EXISTINGAPPLICATIONLINEITEM.TARGETLINEITEMID in (select TARGETLINEITEMID from inserted))) as FINANCIALTRANSACTIONLINEITEM_APPLICATIONAMOUNTS
on FINANCIALTRANSACTIONLINEITEM.ID =FINANCIALTRANSACTIONLINEITEM_APPLICATIONAMOUNTS.TARGETLINEITEMID
group by FINANCIALTRANSACTIONLINEITEM.ID,
FINANCIALTRANSACTIONLINEITEM.AMOUNT
having FINANCIALTRANSACTIONLINEITEM.AMOUNT < sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATIONAMOUNTS.AMOUNT, 0))))
raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_APPLICATIONSTOTALVALID', 13,1);
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
end
|
Trigger Definition
