Trying to run a procedure inside After insert trigger , even though procedure run for 10 mins last row updated time is same as 1st row

Question

Inside the trigger ,I'm trying populate multiple tables and trying to get start and end time of each table population , when run normally it takes 4 mins to populate table so start and end time has 4 mins difference. But when triggered by populating it's trigger table it's showing start and end time same as triggered time ,but my table is getting populated

Table name:Tableupdatestatus

Expected Result

TableName: Start time: Endtime XYZ: 03-05-2024 10:30:00: 03-05-2024 10:34:00

Actual Result in trigger

TableName: Start time: Endtime XYZ: 03-05-2024 10:30:00: 03-05-2024 10:30:00

Answer 1

This behaviour is by design.

From the MySQL manual:

Functions that return the current date or time each are evaluated only once per query at the start of query execution. This means that multiple references to a function such as NOW() within a single query always produce the same result. (For our purposes, a single query also includes a call to a stored program (stored routine, trigger, or event) and all subprograms called by that program.) This principle also applies to CURDATE(), CURTIME(), UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP(), and to any of their synonyms.

You'll find the reference on this page, but you'll need to scroll down.