I am trying to code a trigger that affects two tables at once. Changing the value on a different table is giving me no problem, so I will exclude that from all of my explanations for the sake of brevity.
My table contains the following columns: ID, Parent, URL, Name,SAME
AFTER UPDATE, I want my trigger to SET the URL
of any row to the URL
of the NEW row IF that row’s Parent
is equal to the NEW row’s ID
Seems simple…
CREATE TRIGGER aft_INS_Table2 AFTER INSERT ON `Table 2`
FOR EACH ROW
UPDATE `Table2` SET `Table2`.`URL` =NEW.`URL` WHERE `Table 2`.`Parent` = NEW.`ID`
I am able to create the trigger, but when it run I get an error:
Can’t update table ‘Table2’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
Googling it I found suggestions that simply eliminate the UPDATE ( which would make sense)
CREATE TRIGGER aft_INS_Table2 AFTER INSERT ON `Table 2`
FOR EACH ROW
SET `Table2`.`URL` =NEW.`URL` WHERE `Table 2`.`Parent` = NEW.`ID`
But when I tried it the trigger would not be created. (errors as: … unknown sys variable URL
)
Another find was to use an IF THEN clause instead of where ( and NEW.Col_name
) is used… but that logic will not perform what I need as I am not trying to chck the value of the updated data, but to perform an additional update on other rows based on THEIR value IN COMPARISON to a new value…
So this fails the same way:
Create trigger aft_INS_Table2 AFTER UPDATE ON `Table2`
FOR EACH ROW
IF `Table2`.`Parent`= NEW.`ID` THEN SET `Table2`.`URL`=NEW.`URL`
and this makes no sense for nearly obvious reasons:
Create trigger aft_INS_Table2 AFTER UPDATE ON `Table2`
FOR EACH ROW
IF NEW.`Parent`= NEW.`ID` THEN SET NEW.`URL`=NEW.`URL`
and neither does this
Create trigger aft_INS_Table2 AFTER UPDATE ON `Table2`
FOR EACH ROW
IF OLD.`Parent`= NEW.`ID` THEN SET OLD.`URL`=NEW.`URL`
so am stumped, any suggestions as to how to code this?