CODEWITHSUNDEEP
mysqldatetriggerstimestampprocedure
Lucian0c
Lucian0c

Reputation: 13

Updating a column on MYSQL with NOW doesn't work properly

I need to insert a record on a table with a Now() to measure the time that a trigger consume. I Have a table named "MyTable" with a trigger (Before Insert) like this:

create trigger ...

Set @startTime = NOW();

-- Do complex an expensive operations on the MyTable..

Set @endTime = NOW();

Insert into myLogTable(tableName, Start, End, Creation)
Values ('MyTable', @startTime, @endTime, NOW());

The problem is that the table always get the same value in the columns Start, End and Creation, even if a put a Sleep(10) to get a big differences. It's like mysql is chaning the CURRENT_TIMESTAMP with a constant value.

myLogTable is filled with something like:

I try with no success with a timestamp function or column, and i always get the same value in the 3 columns.

How can i make a log with the time that the trigger consume in each call?

Upvotes: 1

Views: 628

Answers (1)

spencer7593
spencer7593

Reputation: 108400

The NOW() function is evaluated at the start of the SQL statement; it returns a value which remains constant for the duration of the SQL statement execution. Every reference to NOW() within the statement returns the same value. This same constant value is returned by any references in functions or or triggers that are executed by the statement.

The SYSDATE() function is different in that it returns the datetime value when it is executed.

Compare:

SELECT NOW(), SLEEP(5), NOW(); 
SELECT SYSDATE(), SLEEP(5), SYSDATE();

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_sysdate

Upvotes: 1

Related Questions