On 2019-11-22 11:02, Alan J Davies alan.dav...@aldis-systems.co.uk
[firebird-support] wrote:
> Hi all, is it possible to update a table when the current_date in
> rdb$database changes to the next day?
>
> I need to reset a delivery counter to 0 in all the orders on a system.
> I can presently do this two ways.
>
> When I print a delivery label I do it via a SP...
> cut-down start of SP
> select current_date
> fromrdb$database
> into:human_date;
> select p.label_counter_date
> fromparamfil p
> into:counter_date;
> if (:human_date>:counter_date) then -- it must be the next day
> begin
> update paramfil p
> set p.label_counter_date=current_date;
> update orditems oi
> set oi.label_day_count=0;
> end
> then continue with SP to print labels as normal
> This does cause a delay to the first set of delivery labels printed
> each
> day.
>
> I could write and run a small Delphi program which would only have the
> above code as an active item. I could then use Windows (its a Windows
> 2016 server) to run this program at midnight plus a minute, as a
> scheduled task.
>
> But that seems a lot for something I think I should do within the
> database itself. I've tried to create a trigger on rdb$database but
> cannot, I don't have it available and I understand that I should not
> 'mess' with it anyway.
>
> Any help or suggestions gratefully received.
Firebird doesn't have a task scheduler, and there is no physical
database change when the date changes, so you can't trigger an event.
current_database is a function (or context variable), not a column in a
database.
Mark
