There is a timetable table 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 columns id , user ... time inserted after the INSERT action.

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

  • 3
    And why do you need it after, of course, it cannot change NEW because it was called after the recording hit the disk. Use the BEFORE insert. - Mike
  • Well, it is somehow more logical to make a record after the INSERT event, so I chose AFTER, but what if I have a record that pulls out the last row from the table and does some transformations on some fields? BEFORE will not work here, because the last line will not be there yet and it will take the existing previous line. Of course, you can use BEFORE and NEW to refer to the fields of the inserted record and do some transformations, but in some cases it seems to me not quite obvious than AFTER NEW - MaximPro
  • ... although it is strange that after the INSERT event, SQL cannot update some field (although if we didn’t insert any value there before, then what update is it about?) DISK? - MaximPro
  • one
    NEW means an entry in the RAM, the BEFORE trigger is triggered, then it writes a write to the disk, then AFTER is triggered. The second time to flush the changes to the disk to NEW, well, it's like something quite strange. This is at least +1 I / O operation, and considering that in a transactional table, any record requires at least 3 physical operations on the disk, waiting for each to complete, then we get 6 instead of 3 at once. to another block due to the increase in its size - Mike
  • And if you need some kind of operation that reaches the last row, then you generally have to do a trigger not for each row, but for the whole operation - Mike

1 answer 1

The keywords NEW and OLD can only be used in the 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 in relation to the current table - MySQL will not allow to change it. However, if your task is to change the time of only one column being inserted, 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 table

 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.