CODEWITHSUNDEEP
mysqltriggers
Mario S
Mario S

Reputation: 1984

Set a datetime column with a SQL trigger

I using a MySQL server (5.5.27 - Community Server). I have a table with this definition:

CREATE  TABLE IF NOT EXISTS tbl_messages (
  `msg_id` VARCHAR(50) NOT NULL ,
  `msg_text` VARCHAR(50) NULL ,
  PRIMARY KEY (`msg_id`);

I write a trigger that, when I do an insert, the server sets the msg_id column with the current time including microseconds with this format "yyyymmddhhnnssuuuuuu". "u" is for microseconds.

I created a trigger:

create trigger tbl_messages_trigger
before insert on tbl_messages 
for each row

BEGIN
    SET NEW.msg_id = DATE_FORMAT(NOW(),'%Y%m%d%H%i%s%f'); 
END;$$

But the msg_id column only gets values like this: 20130302144818*000000*, with microseconds in zero. ¿Is it possible capture the microseconds?

TIA,

Upvotes: 0

Views: 904

Answers (1)

Pavel Malinnikov
Pavel Malinnikov

Reputation: 359

From the code provided I guess that you are trying to use microsecond to minimize probability of getting same msg_id for different rows.

Also, msg_id is the primary key, which should not present any object-specific data but only be unique. There is a good link about: http://en.wikipedia.org/wiki/Surrogate_key

The best way to deal with primary keys in MySql is AUTO_INCREMENT column attribute. If you need insert time for messages, you may provide column for it:

CREATE TABLE tbl_messages 
(
   `msg_id` bigint(20) NOT NULL AUTO_INCREMENT,
   `msg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   `msg_text` VARCHAR(50) NULL,
    PRIMARY KEY (`msg_id`)
);

Upvotes: 1

Related Questions