The task is as follows: when updating a table entry, you need to create a trigger that adds the user, table name, field name, action and update time to the change table.

I quote the SQL code for creating a trigger:

DELIMITER $$ CREATE TRIGGER upd_check AFTER UPDATE ON xyz_clients FOR EACH ROW BEGIN INSERT INTO xyz_changes(user, table_name, record, action, date) VALUES(CURRENT_USER, ???, ???, 'update', CURRENT_DATE); END

We need to somehow get the name of the updated page and record. Thanks in advance for your help.

  • one
    Well, you know the name of the table, it is xyz_clients, the trigger is created for it and you will not get it anymore. And what is the 'field name' I don’t understand at all, it can update many different fields ... - Mike
  • @Mike Exactly, just as it can update many different entries in one UPDATE request. - Vladimir Pavluk
  • @VladimirPavluk Several entries can not, he has a trigger for each row means it will be executed separately for each entry - Mike
  • Really missed - Vladimir Pavluk

1 answer 1

Because in MySQL, one trigger can be attached to only one table, then you know its name - this is xyz_clients

The name of the changed field is a little more complicated. You need to check whether this particular field has changed.

 CREATE TRIGGER upd_check AFTER UPDATE ON xyz_clients FOR EACH ROW BEGIN IF (NOT OLD.field1 <=> NEW.field1) THEN INSERT INTO xyz_changes(user, table_name, record, action, date) VALUES(CURRENT_USER, 'xyz_clients', 'field1', 'update', CURRENT_DATE); END IF; IF (NOT OLD.field2 <=> NEW.field2) THEN INSERT INTO xyz_changes(user, table_name, record, action, date) VALUES(CURRENT_USER, 'xyz_clients', 'field2', 'update', CURRENT_DATE); END IF; .................. IF (NOT OLD.fieldN <=> NEW.fieldN) THEN INSERT INTO xyz_changes(user, table_name, record, action, date) VALUES(CURRENT_USER, 'xyz_clients', 'fieldN', 'update', CURRENT_DATE); END IF; END