create trigger TR_RECEIVABLECREDITLINEITEM_BILLINGITEMVALID
on dbo.RECEIVABLECREDITLINEITEM
after insert,update
not for replication
as begin
begin try
if (exists (select INSERTED.ID
from INSERTED)) and
(not exists(
select INSERTED.ID
from INSERTED
inner join dbo.FINANCIALTRANSACTIONLINEITEM
on INSERTED.ID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join dbo.BILLINGITEM
on INSERTED.BILLINGITEMID = BILLINGITEM.ID
left outer join dbo.BILLINGITEMPRICE
on BILLINGITEMPRICE.BILLINGITEMID = BILLINGITEM.ID
left outer join dbo.BILLINGITEMDATES
on BILLINGITEMDATES.BILLINGITEMID = BILLINGITEM.ID
where (BILLINGITEMPRICE.DATERANGETYPECODE=0 or (BILLINGITEMPRICE.STARTDATE<=FINANCIALTRANSACTION.[DATE] AND (BILLINGITEMPRICE.ENDDATE>=FINANCIALTRANSACTION.[DATE] OR BILLINGITEMPRICE.ENDDATE is null))) or
(BILLINGITEMDATES.DATERANGETYPECODE=0 or (BILLINGITEMDATES.STARTDATE<=FINANCIALTRANSACTION.[DATE] AND (BILLINGITEMDATES.ENDDATE>=FINANCIALTRANSACTION.[DATE] OR BILLINGITEMDATES.ENDDATE is null)))
))
raiserror('ERR_INVALID_CREDITDATE', 13, 1);
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
end
|
Trigger Definition
