I have discovered that the value returned by current_timestamp is
fixed within the scope of any given stored procedure call. So during
the scope of such a call any directly (via a call to another
procedure) or indirectly (via a trigger firing in response to record
manipulations) called stored program units will also return the same
value. In other words, If I have a stored procedure that runs for 1
hour then the value returned by current_timestamp will be that same
at the end of the procedure as it was at the beginning. Such
behavior severely limits the usefulness of this variable to perform a
chronological audit.

Here is some sample call to reproduce this problem.

create table timestamp_test
(
test_set integer,
trigger_tstamp timestamp,
proc_tstamp timestamp
);


create trigger timestamp_test_rbi for timestamp_test
active before insert position 1000
AS
BEGIN

new.trigger_tstamp = current_timestamp;

END;


create procedure timestamp_test_1_prc as

declare variable i integer;

BEGIN

i = 0;

while (i<100000) do

BEGIN

i = i + 1;

insert into timestamp_test(test_set, proc_tstamp) values (1,
current_timestamp);

END

END;


create procedure timestamp_test_2_prc as

declare variable i integer;

BEGIN

i = 0;

while (i<100000) do

BEGIN

i = i + 1;

execute statement 'insert into timestamp_test(test_set,
proc_tstamp) values (2, current_timestamp);';

END

END;

execute procedure timestamp_test_1_prc;
execute procedure timestamp_test_2_prc;

commit;

select test_set,
proc_tstamp,
trigger_tstamp,
count(*)
from timestamp_test
group by test_set,
proc_tstamp,
trigger_tstamp;


-- Expected Output
-- TEST_SET PROC_TSTAMP TRIGGER_TSTAMP COUNT
-- 1 09/14/2003 20:55:15 09/14/2003 20:55:15 100,000
-- 2 09/14/2003 20:55:22 09/14/2003 20:55:22 1,005
-- 2 09/14/2003 20:55:23 09/14/2003 20:55:23 1,109
-- 2 09/14/2003 20:55:24 09/14/2003 20:55:24 1,053
-- 2 09/14/2003 20:55:25 09/14/2003 20:55:25 1,081
-- 2 09/14/2003 20:55:26 09/14/2003 20:55:26 1,099
-- 2 09/14/2003 20:55:27 09/14/2003 20:55:27 1,094
-- 2 09/14/2003 20:55:28 09/14/2003 20:55:28 1,157
-- 2 09/14/2003 20:55:29 09/14/2003 20:55:29 1,100
...

Bob