Re: Timestamp fields

[Sebastian Jurk]

Are you sure you wanted to answer my question? Because i dont know what you are speaking about. I wanted to have 3 timestampfields. The first i set manually with queries because the time doesnt match the creation of the recordset, the second should tell me when the recordset was created and the third should tell me when the recordset was changed the last time. Which means when he was created too at the same time.

Anyway... i found a workaraound some hours ago.

I created a field timestamp that i set manually with queries because it resembles the time of a trade happening. It doesnt matter what standard or on update clause this has.
Then i created the fields rs_create and rs_update. Both are timestamp too and have the standardvalue zero.
Then i create 2 triggers:

CREATE TRIGGER `market_trades_rs_create` BEFORE INSERT ON `market_trades`
FOR EACH ROW set NEW.rs_create = CURRENT_TIMESTAMP, NEW.rs_update = CURRENT_TIMESTAMP;

CREATE TRIGGER `market_trades_rs_update` BEFORE UPDATE ON `market_trades`
FOR EACH ROW set NEW.rs_update = CURRENT_TIMESTAMP;

This way i can insert a new recordset into that table and set the field timestamp manually. The 2 other fields are automatically updated and show the last time of change. That is the creationtime of the recordset in rs_create and the creationtime of the recordset or the last update of the recordset in rs_update.

Hope that helps some others that struggle with (keywords:) Timestamp, datetime, on update current timestamp, current timestamp, #1293 - Incorrect table definition; there can be only one timestamp column with current_timestamp in DEFAULT or ON UPDATE clause.

Greetings!
Sebastan