Automatically update timestamp in update

Question

Automatically update timestamp in update

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

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

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

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

Follow my table:

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

asked by anonymous 03.11.2017 / 12:17

2 answers

Convert DataTable to List C # "gen_id ()" or "select max ()" for Auto Increment in a PK field?

score 2 · Answer 1

2

Changes in table creation to:

'dt_atualizacao' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

and see if it works.

03.11.2017 / 12:28

score 0 · Answer 2

From what I saw in the previous comments and responses, I only see a form that has not yet tried (or did not find the best). Pass the value in INSERT and in 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;