CREATE trigger TR_FINANCIALTRANSACTIONLINEITEM_REVERSEDLINEITEMID
on dbo.FINANCIALTRANSACTIONLINEITEM
for insert, update
not for replication as
begin
set nocount on;
begin try
if exists(
select inserted.ID
from inserted
left outer join dbo.FINANCIALTRANSACTIONLINEITEM
on inserted.REVERSEDLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
where ((inserted.ID != FINANCIALTRANSACTIONLINEITEM.ID) or (FINANCIALTRANSACTIONLINEITEM.ID is null)) and
/* Linked line item is not part of the same transaction (error case) */
(((inserted.FINANCIALTRANSACTIONID != FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID) and (not FINANCIALTRANSACTIONLINEITEM.ID is null)) or
/* not a reversal with a linked line item (error case) */
((inserted.TYPECODE != 1) and (not FINANCIALTRANSACTIONLINEITEM.ID is null)) or
/* A reversal without a linked line item, or the linked item is not deleted, or the linked item is of type reversal (you cannot reverse a reversal) */
((inserted.TYPECODE = 1) and ((FINANCIALTRANSACTIONLINEITEM.ID is null) or (FINANCIALTRANSACTIONLINEITEM.DELETEDON is null) or (FINANCIALTRANSACTIONLINEITEM.TYPECODE = 1))))
)
raiserror('ERR_FINANCIALTRANSACTIONLINEITEM_REVERSEDLINEITEMID', 13,1);
if exists(
select inserted.REVERSEDLINEITEMID
from inserted
inner join dbo.FINANCIALTRANSACTIONLINEITEM
on inserted.REVERSEDLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
group by inserted.REVERSEDLINEITEMID
having (count(inserted.REVERSEDLINEITEMID) > 1)
)
raiserror('ERR_FINANCIALTRANSACTIONLINEITEM_CANNOTHAVEMORETHANONEREVERSALPERLINEITEMID', 13,1);
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
end
|
Trigger Definition
