phpmyadmin Question posted in
The official documentation can be found here.

Mysql After Update/İnsert Triggers and 1442-Error – Phpmyadmin

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search