CREATE trigger TR_FINANCIALTRANSACTION_UPDATE on dbo.FINANCIALTRANSACTION after update not for replication
as begin
set nocount on;
if UPDATE(CONSTITUENTID)
begin
-- resets REMITADDRESS on invoices when changing vendor
update dbo.INVOICE set
REMITADDRESSID = null
,CHANGEDBYID = CASE WHEN UPDATE(CHANGEDBYID) THEN I.CHANGEDBYID ELSE NULL END -- force the invoice trigger to take care
,DATECHANGED = CASE WHEN UPDATE(DATECHANGED) THEN I.DATECHANGED ELSE NULL END -- force the invoice trigger to take care
from
dbo.INVOICE as INV
inner join inserted I on INV.ID = I.ID AND I.TYPECODE=101 -- invoice
inner join deleted D on I.ID = D.ID
where
I.CONSTITUENTID<>D.CONSTITUENTID
-- resets REMITADDRESS on credit memos when changing vendor
update dbo.INVOICE set
REMITADDRESSID = null
,CHANGEDBYID = CASE WHEN UPDATE(CHANGEDBYID) THEN I.CHANGEDBYID ELSE NULL END -- force the invoice trigger to take care
,DATECHANGED = CASE WHEN UPDATE(DATECHANGED) THEN I.DATECHANGED ELSE NULL END -- force the invoice trigger to take care
from
dbo.INVOICE as INV
inner join inserted I on INV.ID = I.ID AND I.TYPECODE=101 -- invoice
inner join deleted D on I.ID = D.ID
where
I.CONSTITUENTID<>D.CONSTITUENTID
end;
-- Prevent editing based on post status and payment status
if (update(CONSTITUENTID) or update(AMOUNT))
and exists
(
select D.ID
from INSERTED I
inner join DELETED D ON I.ID = D.ID
inner join FINANCIALTRANSACTIONLINEITEM D_FTLI on D.ID = D_FTLI.FINANCIALTRANSACTIONID
WHERE
(D.CONSTITUENTID <> I.CONSTITUENTID or D.AMOUNT <> I.AMOUNT)
and (
D_FTLI.POSTSTATUSCODE=2 -- it's posted
or dbo.UFN_FINANCIALTRANSACTION_PAID(I.ID) = 1 -- Performance : may consider writing and using a tvf here.
)
)
raiserror ('ERR_FINANCIALTRANSACTION_LOCKEDFIELDS', 16, 1);
-- Prevent changing accounting system
if update (PDACCOUNTSYSTEMID) and exists( select I.ID from inserted as I inner join deleted as D on I.ID = D.ID where I.PDACCOUNTSYSTEMID<>D.PDACCOUNTSYSTEMID)
raiserror ('ERR_FINANCIALTRANSACTION_LOCKEDPDACCOUNTSYSTEM', 16, 1);
end
|
Trigger Definition
