mysql – Триггеры SQL, AFTER INSERT

Question:

There is a timetable with 3 fields ( id , time , user )

CREATE TRIGGER addTime
AFTER INSERT ON timetable
FOR EACH ROW
BEGIN
  SET NEW.time = now();
END

How it should work: Insert a record with values ​​for the id columns, user … time inserted after the INSERT action.

But this does not work because the program swears at AFTER INSERT

Answer:

The NEW and OLD keywords can only be used in a BEFORE trigger. A correct trigger might look like this:

DELIMITER //
CREATE TRIGGER addTime
BEFORE INSERT ON timetable
FOR EACH ROW BEGIN
  SET NEW.time = NOW();
END//

You can call UPDATE in the AFTER trigger to update the data, but not on the current table – MySQL will not let you change it. However, if your task is to change the time of only one inserted column, there is no need to use a trigger, it is enough to set the ON UPDATE condition for the time column when defining the timetable

CREATE TABLE timetable (
  id int(11) NOT NULL,
  ...
  `time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Such a column can be left blank – it will automatically be assigned the current time.