If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: how to trace insert statement time

Hi I want to know the insert time of every row inserted in a particular table.
How can I achieve it.
My requirement is to trace the insert time of each row in table A
without changing the structure of table A so i have created another table
B having one primary key same as table A and two more columns start and end
start is the starting time and end is the ending time.

Now how to capture the start and end time of each row inserted in table A.
I tried with trigger but not able to create trigger on before insert event as i cant use INSERT statement in BEFORE INSERT trigger

why you want to capture before n after time. usually inserts happens in microsecond range.... or if your inserts takes very long time i think a current timestamp before and after the insert might give you idea

Rahul,
I am not talking about one insert or 2 inserts.
the table contain 20000 insertion per min through application and is not possible for me to take the time before and after insertions.
Thats why i need to make it automatized

You have a misconception about the "current timestamp" in a SQL statement...

Each SQL statement is executed at a single point in time only and the duration of the execution is not relevant. That means, whenever a SQL statement starts executing, it grabs the current timestamp value and "freezes" that for its own execution. Thus, the timestamps in a before and after trigger are always the same because triggers are compiled into the INSERT statement and, therefore, use the same frozen current timestamp.

This is standard SQL behavior, and it makes perfect sense because each SQL statement is supposed to be "atomic". And having a duration for atomic operations is a bit counter-intuitive. Granted, it is impossible to execute statements without any duration, but conceptually it is possible to give applications such a view and that is what's done.

If you need the real timestamp when the statement completed, you have to resort to some external facility to determine the timestamp, e.g. use a UDF to retrieve the information from an independent facility like the operating system. But note that "statement completed" is not really true there either. The trigger calling the UDF is executed as part of the INSERT statement, so the execution is not really completed yet.