Subject [firebird-support] Re: Daily triggers
Author Svein Erling Tysvær
>> just call your procedure from isql in a script run by cron (unix-based)
>Thanks. You and Anderson suggested the same idea. I see a problem, however.
>Picture an end user thinking that if the scheduled job is good for one computer,
>it must be better for all network computers. In fact, the procedure should only
>be run once each day. We all know how "creative" end users can be. Apparently I
>need to make the procedure smart enough to only allow itself to be run once on
>any given day.
>It would be nice if Firebird of the future would support something like that. It
>is difficult to influence user controlled things like scheduled tasks when the
>product is widely distributed.

If the procedure should run maximum once per day, add a table that contains only one record with a field like DateLastRun and then start your procedure by checking the current value of this field and update it if it is less than today, quit your procedure otherwise. If you then accidentally run the procedure twice simultaneously, the second transaction would hopefully end up with a lock conflict and you can just rollback rather than commit.

You can never control your users 100%. However, if you write in your user manual that the scheduled event should only be on the server, then many would adhere. If it does no harm (i.e. the result of running the procedure twice would be identical, just time consuming), I'd say that could be good enough. When your users doesn't follow the manual, it's their fault if that leads to the server being busy doing redundant work. If the result from the second run would differ from the first (e.g. adding something twice rather than once), I'd say you might take another look at your logic to see whether it is flawed. If it is not flawed, just implement something like the paragraph above.