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


Thanks...

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

hope now you can understand the scenario.

You need an AFTER INSERT trigger.

HI,
After trigger will give me the timestamp of after insert.
now ho can i get the insertion time. with before insert trigger.
and before trigger is not working with INSERT statement.

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.

Here is an example that you can try yourself:
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.

HI Stolze
Thanks for clarificaiton.....!!!!!!!!