TRIGGER that affects the same table?

[dresden_phoenix]

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?

[rcashell]

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?

[swampBoogie]

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

[rcashell]

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

[dresden_phoenix]

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. :/ 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.