Method to prevent two NOW() columns from being updated at the same time when updating NOW() of one column
P粉043566314
P粉043566314 2024-04-04 21:34:34
0
1
475

I have a table that requires two different current times. First, I have an insert procedure that inserts actionnumber, msgSentFrom_F_ID, msgSentTo_M_ID and sentDate. Secondly, update the update process of respondDate. My problem is that when I update respondDate, sentDate is updated to the same time as when I update respondDate. What did I do wrong? (My intention is that I want the sent date to be the current time when I insert, and another current time when I update the response date.)

CREATE TABLE IF NOT EXISTS actions (
  actionnumber INT AUTO_INCREMENT PRIMARY KEY,
  msgSentFrom_F_ID INT, 
  msgSentTo_M_ID INT,
  sentDate TIMESTAMP,
  respondDate TIMESTAMP NULL,
  FOREIGN KEY (msgSentFrom_F_ID) 
    REFERENCES femaleUsers(femaleuserId)
  FOREIGN KEY (msgSentTo_M_ID) 
    REFERENCES maleUsers(maleuserId)
);

DELIMITER //
create procedure (param_F_ID INT,param_M_ID INT,Sdate TIMESTAMP)
  BEGIN
  INSERT INTO actions (msgSentFrom_F_ID, msgSentTo_M_ID, sentDate) 
    VALUES (param_F_ID,param_M_ID,Now());
  END; //
DELIMITER ;

CALL insert_actions ('5','5',NOW());

DELIMITER //
create procedure update_respondDate (param_ActionNum INT, 
    param_respondDate TIMESTAMP)
  BEGIN
  UPDATE actions set respondDate = param_respondDate 
  WHERE  actionnumber = param_ActionNum;
  END; //
DELIMITER ;

CALL update_respondDate('6',NOW());

P粉043566314
P粉043566314

reply all(1)
P粉574695215

Sounds like you disabled the system variable explicit_defaults_for_timestamp . Documentation explains this result: p>

Since sentDate is the first TIMESTAMP column in the table, it will automatically be set to the current time whenever you make any changes to that row.

Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template