Mysql After Update/İnsert Triggers and 1442-Error

Question

I want to see new records and updates entered in the log table, I created a trigger for this but I get error 1442. Can you help me, please?

CREATE TRIGGER `trigger_changes`
    AFTER UPDATE ON `customers`
    FOR EACH ROW 
    Update customers c INNER JOIN cust_changes ch ON ch.id = c.id SET ch.name = c.name

1442 - Can't update table 'cust_changes' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Answer 1

You have to reference the data in the trigger using the pseudo table name OLD or NEW.

Update cust_changes
SET name = NEW.name
WHERE id=NEW.id

Answer 2

If you use OLD.id (or NEW.id) in the updated statement then mysql (as @PeterHe) suggested is happy. If your model is different or you are doing something different then you need to tell us.

drop table if exists t,t1;
create table t(id int, name varchar(3));
create table t1(id int,name varchar(3));

insert into t values(1,null);
insert into t1 values(1,null);

CREATE TRIGGER t
    AFTER UPDATE ON t
    FOR EACH ROW 
    Update t INNER JOIN t1  ON t1.id = old.id 
        SET t1.name = t.name;

update t set name = ('aaa');

select * from t1;

+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+
1 row in set (0.00 sec)

As expected with no errors. Please add your versions for mysql and phpmyadmin.