Automatically update timestamp on update

Asked

Viewed 1,610 times

0

Using the UPDATE CURRENT_TIMESTAMP it is possible to automatically update a field from a table in the Mysql database?

I want the inclusion date to be assigned to the fields: dt_cadastro and dt_atualizacao.

And when a change is made, the field dt_atualizacao is updated with the modification date.

Follow my schedule:

DROP TABLE IF EXISTS `tbl_devedor`;
CREATE TABLE IF NOT EXISTS `tbl_devedor` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(255) NOT NULL,
  `sistema` varchar(5) NOT NULL,
  `id_assessoria` INT(11) NOT NULL,
  `dt_cadastro` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `dt_atualizacao` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `situacao` TINYINT(1) NULL DEFAULT NULL,
   PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
mysql

  • creates a Rigger to fire update tbl_devedor set dt_atualizacao = current_timestamp where id = new.id;

  • Is giving some error currently?

  • @Andersoncarloswoss Error does not give, but is not updating.

  • What SQL statement did you use to update?

2 answers

3

Changes the table creation to:

`dt_atualizacao` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

and see if it works.

  • It doesn’t work, I’ve tried it this way.

  • This is the right solution. The problem is if the author is using an outdated Mysql (which seems to be the case), then only the first timestamp column will work.

  • @Bacco, I’m using version 5.7.11

  • @Wagnerson then this solution must work. Note that in your original query ON UPDATE is in the wrong place (it should be in dt_update and not in dt_registration. The manual itself mentions that this has been implemented from 5.6.5 onwards: https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

0

From what I’ve seen in previous comments and responses I only see one way I haven’t tried to do (or didn’t think it was the best one). Pass the value on INSERT and us UPDATES

INSERT INTO `tbl_devedor` (`nome`, `sistema`, `id_assessoria`, `dt_cadastro`,
  `dt_atualizacao`, `situacao`) VALUES ("Maria", "SistemaXX",54, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 1);

UPDATE `tbl_devedor` SET `dt_atualizacao`= CURRENT_TIMESTAMP, `nome`="Maria Fontes" WHERE  `id`=15;

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.