SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Community Advisor silver trophybronze trophy
    dresden_phoenix's Avatar
    Join Date
    Jun 2008
    Location
    Madison, WI
    Posts
    2,798
    Mentioned
    34 Post(s)
    Tagged
    2 Thread(s)

    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...
    Code:
     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)
    Code:
     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:
    Code:
    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:
    Code:
    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
    Code:
    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?

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    It should be possible to circumvent this limitation in Mysql, by calling a stored procedure that does the actual update.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Or you could normalize the database to have a single value represent the URL for a group of rows.

  5. #5
    Community Advisor silver trophybronze trophy
    dresden_phoenix's Avatar
    Join Date
    Jun 2008
    Location
    Madison, WI
    Posts
    2,798
    Mentioned
    34 Post(s)
    Tagged
    2 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •