CREATE trigger TR_FINANCIALTRANSACTION_UPDATE on dbo.FINANCIALTRANSACTION after update, insert not for replication
as begin
set nocount on;
declare @IsAP bit = 0
if exists(select 1 from inserted where TYPECODE > 100)
set @IsAP = 1
--TR_REVENUE_INSERT_BBNCREVENUEIDMAP
insert into dbo.BBNCREVENUEIDMAP (REVENUEID)
select
inserted.ID
from inserted
left outer join deleted on inserted.ID = deleted.ID
where
inserted.TYPECODE in (0,1,2,3,4,5,6,7,8) and deleted.ID is null
and not inserted.ID is null
if UPDATE(CONSTITUENTID) and (@IsAP = 1)
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(TRANSACTIONAMOUNT)) and (@IsAP = 1)
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.TRANSACTIONAMOUNT <> I.TRANSACTIONAMOUNT)
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
inner join dbo.FINANCIALTRANSACTIONLINEITEM as L on L.FINANCIALTRANSACTIONID = D.ID
inner join dbo.JOURNALENTRY as J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
where I.PDACCOUNTSYSTEMID<>D.PDACCOUNTSYSTEMID)
raiserror ('ERR_FINANCIALTRANSACTION_LOCKEDPDACCOUNTSYSTEM', 13, 1);
--TR_REVENUE_MARKASCONSTITUENT
if update(CONSTITUENTID)
update dbo.CONSTITUENT
set ISCONSTITUENT = 1,
CHANGEDBYID = inserted.CHANGEDBYID,
DATECHANGED = GetDate()
from inserted inner join CONSTITUENT on inserted.CONSTITUENTID = CONSTITUENT.ID
where CONSTITUENT.ISCONSTITUENT = 0
and inserted.TYPECODE in (0,1,2,3,4,5,6,7,8)
--Put FK cascade deletes to FinancialTransaction here
if update(DELETEDON)
begin
if exists (select 1 from dbo.INSTALLMENT inner join inserted on INSTALLMENT.REVENUEID = inserted.ID where DELETEDON is not null)
delete INSTALLMENT from dbo.INSTALLMENT inner join inserted on INSTALLMENT.REVENUEID = inserted.ID where DELETEDON is not null
if exists (select 1 from dbo.INSTALLMENTSPLITWRITEOFF inner join inserted on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = inserted.ID where DELETEDON is not null)
delete INSTALLMENTSPLITWRITEOFF from dbo.INSTALLMENTSPLITWRITEOFF inner join inserted on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = inserted.ID where DELETEDON is not null
if exists (select 1 from dbo.SALESORDERPAYMENT inner join inserted on SALESORDERPAYMENT.PAYMENTID = inserted.ID where inserted.DELETEDON is not null)
delete SALESORDERPAYMENT from dbo.SALESORDERPAYMENT inner join inserted on SALESORDERPAYMENT.PAYMENTID = inserted.ID where inserted.DELETEDON is not null
if exists (select 1 from dbo.SALESORDER inner join inserted on SALESORDER.REVENUEID = inserted.ID where inserted.DELETEDON is not null)
update SALESORDER set REVENUEID = null, CHANGEDBYID = inserted.CHANGEDBYID, DATECHANGED = getdate() from dbo.SALESORDER inner join inserted on SALESORDER.REVENUEID = inserted.ID where inserted.DELETEDON is not null
if exists (select 1 from dbo.PLANNEDGIFTRECONCILE inner join inserted on PLANNEDGIFTRECONCILE.REVENUEID = inserted.ID where inserted.DELETEDON is not null)
delete PLANNEDGIFTRECONCILE from dbo.PLANNEDGIFTRECONCILE inner join inserted on PLANNEDGIFTRECONCILE.REVENUEID = inserted.ID where inserted.DELETEDON is not null
if exists (select 1 from dbo.PLANNEDGIFTREVENUE inner join inserted on PLANNEDGIFTREVENUE.REVENUEID = inserted.ID where inserted.DELETEDON is not null)
update PLANNEDGIFTREVENUE set REVENUEID = null, CHANGEDBYID = inserted.CHANGEDBYID, DATECHANGED = getdate() from dbo.PLANNEDGIFTREVENUE inner join inserted on PLANNEDGIFTREVENUE.REVENUEID = inserted.ID where inserted.DELETEDON is not null
if exists (select 1 from dbo.RECURRINGGIFTINSTALLMENT inner join inserted on RECURRINGGIFTINSTALLMENT.REVENUEID = inserted.ID where inserted.DELETEDON is not null)
delete RECURRINGGIFTINSTALLMENT from dbo.RECURRINGGIFTINSTALLMENT inner join inserted on RECURRINGGIFTINSTALLMENT.REVENUEID = inserted.ID where inserted.DELETEDON is not null
if exists (select 1 from dbo.RECURRINGGIFTINSTALLMENTPAYMENT inner join inserted on RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID = inserted.ID where inserted.DELETEDON is not null)
delete RECURRINGGIFTINSTALLMENTPAYMENT from dbo.RECURRINGGIFTINSTALLMENTPAYMENT inner join inserted on RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID = inserted.ID where inserted.DELETEDON is not null
update FINANCIALTRANSACTIONLINEITEM
set DELETEDON = inserted.DELETEDON,
CHANGEDBYID = inserted.CHANGEDBYID,
DATECHANGED = getdate()
from dbo.FINANCIALTRANSACTIONLINEITEM inner join inserted on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = inserted.ID
where inserted.DELETEDON is not null
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
end
end
|
Trigger Definition
