CREATE trigger TR_INVOICE_INSERT_UPDATE on dbo.INVOICE after insert,update not for replication
as begin
begin try
if exists(
select I.ID
from INSERTED I
inner join dbo.FINANCIALTRANSACTION F on F.ID = I.ID
where F.TRANSACTIONAMOUNT < I.BALANCE)
begin
raiserror('The balance must be less than or equal to the invoice amount.', 13, 1);
end
if exists(
select V.ID
from INSERTED I
inner join dbo.FINANCIALTRANSACTION F on F.ID = I.ID
left outer join dbo.VENDOR V on V.ID = F.CONSTITUENTID
where
V.ID is null
)
begin
raiserror('The constituent must be a payables vendor.', 13, 1);
end
if exists(
select I.ID
from INSERTED I
inner join dbo.FINANCIALTRANSACTION F on F.ID = I.ID
left outer join dbo.ADDRESS A on (A.CONSTITUENTID = F.CONSTITUENTID) and (A.ID = I.REMITADDRESSID)
where
I.REMITADDRESSID is not null
and A.ID is null
)
begin
raiserror('The remit to address must belong to the selected vendor.', 13, 1);
end
-- handle locking of certain fields based on post status and payment status
if update(DISCOUNTAMOUNT)
if exists
(
select D.ID
from INSERTED I
inner join DELETED D ON I.ID = D.ID
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = D.ID
WHERE
( /* paid*/
exists
(
select I.ID
from DELETED I
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = I.ID
inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FT.ID = FTS.FINANCIALTRANSACTIONID
inner join dbo.FINANCIALTRANSACTIONAPPLICATION FTA on FTA.FINANCIALTRANSACTIONSCHEDULEID = FTS.ID
where FTA.STATUSCODE <> 2 and FT.ID = I.ID and D.DISCOUNTAMOUNT<>I.DISCOUNTAMOUNT
)
)
)
BEGIN
RAISERROR ('ERR_INVOICE_LOCKEDFIELDS', 16, 1)
END
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
end
|
Trigger Definition
