SQL Trigger to update OLD value in another table

Question

I am trying to create a trigger to simultaneously update a different table than the one I have updated, with the same data.

I have two different database with the same tables and i'm trying to sync them, when i insert, update or delete data from one, i want to do automaticaly the same to the other table, with triggers.

This is the trigger code:

CREATE DEFINER=`Ivan_test`@`%` TRIGGER `Prueba_Ivan`.`mag_articulos_PI_AFTER_UPDATE` AFTER UPDATE ON `mag_articulos_PI` FOR EACH ROW
BEGIN
    IF OLD.Prueba_Ivan.mag_articulos_PI NOT IN (SELECT * FROM ivan_test.mag_articulos_IT) THEN
    INSERT INTO ivan_test.mag_articulos_IT 
            VALUES (new.xempresa_id, new.xarticulo_id, new.xcategoria_id, new.xvisible_web, new.xnovedad,new.xpromocion,new.ximagen_prelim,new.ximagen_amp,new.xtexto1,new.xtexto2,new.xtexto3,new.xtexto4,new.xtexto5);
    ELSE 
    UPDATE ivan_test.mag_articulos_IT SET OLD.ivan_test.mag_articulos_IT = NEW.Prueba_Ivan.mag_articulos_PI;    
    END IF;
END

but I have this error: Error Code: 1109. Unknown table 'OLD.Prueba_Ivan' in IN/ALL/ANY subquery

Can someone help me to find the mistake?

Thank you!!

Where are the table definitions? What is `OLD.Prueba_Ivan.mag_articulos_PI` a reference to? — Scratte, Jul 21 '20 at 09:44
There is no need to qualify a column with a table name - which is what I assume you are trying to do here - OLD.Prueba_Ivan.mag_articulos_PI and if you did I guess as with everything else in sql table name would come first..Also there is no evidence that you are setting delimiters or any way of understanding if you do need to set them given that you have not told us if you are using a wizard in workbench. — P.Salmon, Jul 21 '20 at 10:03
@Scratte OLD.Prueba_Ivan.mag_articulos_PI reference the database Prueba_ivan, table mag_articulos_PI before update. I am trying to check if the row to update is in the other table, if it is not, I add it. — ImvD, Jul 21 '20 at 10:15
I do not think you can reference a database this way. I think MySQL expects the old value to be a column that is on the table `mag_articulos_PI` of the trigger. In addition to the table definitions, it would greatly help if you explained in words what you are trying to do and if you have several databases if you're trying to update data across those. — Scratte, Jul 21 '20 at 10:21
@P.Salmon I need to reference the table this way because I have two different databases, i think the problem is when i try to access to the table before to apply changes. I don't know how i can check if this row is in the other table too. — ImvD, Jul 21 '20 at 10:27
Just to be clear a trigger is ON a table in the session DB and You can reference OLD. and/or NEW. columns on the table the trigger is on. You can insert or update to another DB table but you cannot amend OLD. or NEW. values in that DB table (they don't exist) — P.Salmon, Jul 21 '20 at 10:31
@Scratte true, i'm going to try explain it better in the description. — ImvD, Jul 21 '20 at 10:32
I see you have updated your post. But what are the names of the databases, the names of the tables? The clarifications that you have made in comments, should go into your post. Please see [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) — Scratte, Jul 21 '20 at 15:04
@Scratte Great, thanks for your help. I'll keep the post updated. — ImvD, Jul 21 '20 at 17:54

score 0 · Answer 1 · answered Jul 21 '20 at 10:31

OLD.Prueba_Ivan.mag_articulos_PI

OLD is an alias to the triggered row. Your trigger applies to Prueba_Ivan, which means that OLD and NEW are representing your Prueba_Ivan record before the change, and after it, respectively. This means that when you intend to reference mag_articulos_PI, you will need to do it via OLD.mag_articulos_PI, so remove the tablename from that expression.

OLD.ivan_test.mag_articulos_IT

As mentioned in the previous section, here OLD is an alter-ego of the updated Prueba_Ivan record, you do not need it in order to reference ivan_test.

Further explanation

An expression of the form of

a.b.c

reads as follows:

In database a, table b, column c. When you do something of the like of

OLD.t.c

it reads: In the OLD database, table t, column c.

Thank you, i'm gonna try. How can i check if the row to modify is in the other table? Maybe i have to do it on a trigger before update? — ImvD, Jul 21 '20 at 10:43
@ImvD you are looking for If-exists. See: https://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists — Lajos Arpad, Jul 21 '20 at 10:54

SQL Trigger to update OLD value in another table

1 Answers1

OLD.Prueba_Ivan.mag_articulos_PI

OLD.ivan_test.mag_articulos_IT

Further explanation