--- In firebird-support@yahoogroups.com, Joe Martinez <joe@...> wrote:

>
> Is there any way to get the exact timestamp within a trigger, other
> than using a UDF?
>
> I have a trigger that inserts the current timestamp into a
> field. When I wrote the trigger, I used CURRENT_TIMESTAMP, and I
> unfortunately assumed that it would include the fractions of
> seconds. My application needs to be able to query records, sorted by
> the order in which they were inserted, and whole seconds isn't
> granular enough for that, and it is now causing problems in my
> application. So, I now need to modify my trigger in some way so that
> the timestamp value that it inserts will include the fractions of

seconds.

There is an important difference between the behaviour of
CURRENT_TIMESTAMP and 'NOW'. One of them holds the time constant for
the current operation, the other is the time as of this moment in
time. Consider the query:

update mytable set
sometime = 'now';

If mytable is really large, it is possible that the time changes while
this is all happenning. Sometimes you want the changing time
behaviour, sometimes you want the constant time behaviour. I do not
recall which is which.

>
> I am trying to do some quick damage control now, and I'd like it to
> be as simple as deleting and re-adding the trigger. I have a lot of
> installations out there, and the databases are currently NOT attached
> to FBUDF. I'd like to avoid having to set that up in all of the
> installations out there, so I'm hoping for a non-UDF solution. This
> is FB 1.0, and a Dialect 1 database, by the way.

If you only have windows installs, then using FBUDF seems the sensible
way to go immediately.

After running this:
declare external function getExactTimestamp
timestamp returns parameter 1
entry_point 'getExactTimestamp' module_name 'fbudf';

All you need to do is replace your calls to CURRENT_TIMESTAMP with
getExactTimestamp in your stored procedure. The UDF ships with
Firebird 1.5 (not sure about 1.0 installations though). Actually with
dialect 1 you may need to substitute date for timestamp.

That should buy you some time to fix your design. I doubt that the
exact timestamp logic has enough precision to ensure that every record
gets a unique timestamp.

Furthermore, computer clocks are all over the place. It's a sad fact
of life that a $5 watch holds better time than a $2000 server,
particularly when you go through a UPS and the AC frequency isn't
quite what it expects. What is going to happen to your logic if your
server clock goes forward by 10 seconds and then the w32time service
kicks off every couple of days and pulls the time back to close to
correct?

A better design would be to use a generator to have an UpdateNumber.
Every time you modify the record, instead of storing the time (or in
addition to if holding the last changed time is helpful for other
reasons), call the generator and store it in your UpdateNumber field.

If you use a generator to form your primary key, then you already have
an insert number.

Of course without serialising your inserts and updates (very slow),
there is no way of determining whose insert started first when two
simultaneously hit the database. A generator just ensures each gets a
unique value.

It is possible that a record which received value 100 is not committed
until after the record that received 101, but this is exactly the same
as in your timestamp based design.

Under dialect 3, this would be a bigint. Under dialect 1, I think you
are limited to integer.

Adam