TRIGGER that affects the same table?

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?

I think you cannot use triggers for the operation you are trying to perform. You cannot have a trigger perform a data manipulation on entries from the same table on which the trigger is fired from. Looking through your code it is not clear what you are trying to achieve?

It should be possible to circumvent this limitation in Mysql, by calling a stored procedure that does the actual update.

Or you could normalize the database to have a single value represent the URL for a group of rows.

Thanks for you input guys.

what you are trying to achieve?

I had build a DB to store ‘categories’, as such each categories had an ID. since each category COULD also be a SUB category it had a ‘parent’. the category could be displayed via a particular document ‘template’ which could/or could not be inherited from it’s parent. So if ‘cat2’ is a child of ‘cat1’ ( cat2’s ‘parent’ =cat1’s ‘ID’ ) If you wanted cat2 and all of the children of cat1 to inherit cat1’s template anytime cat1 is updated.

I could have done this by adding a second sql statement in my php that ran along with any update cat1. But I was thinking it pondering be done directly in the DB. i hope that makes it clearer.

Delving into the PHP documentation i have sound a couple of places when it distinctly says triggers do not affect data on the same table that is not the same row being worked on. :confused: I have found some suggested work around , which essentially involve creating a second table , kinda like a buffer, performing my operations there and then having that have a trigger that affects the original table. But that seems more convoluted and potentially even more risky for the data , than doing this via a second SQL statement in the PHP script. Thanks again , both of you, for kicking this around with me.