Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    8

    Unanswered: Trigger problem: Inserting and updating same table

    Hello all,

    I'm having a problem. I want to store information about one of the attributes within the same table in order to be able to access it very quickly. I want to store the properties of the 'name' attribute within the same table. Before telling me I shouldn't to this, the reason is that I have a million tuples and need to be able to access this data very quickly... I have to do this.

    I've created a trigger that doesn't work:

    Code:
    DELIMITER |
    DROP TRIGGER IF EXISTS one_word |
    CREATE TRIGGER one_word AFTER INSERT ON node 
    FOR EACH ROW
    BEGIN
    	-- one word
    	IF NEW.name REGEXP '[a-z]' THEN
    		UPDATE node SET mwe = FALSE WHERE eid = NEW.eid;
    	END IF;
    
    END;
    |
    DELIMITER ;
    I can create the trigger, but the UPDATE statement doesn't work if the IF statement is true. Here's my error message:
    Code:
    mysql> INSERT INTO node (eid, name) VALUES (1, "hello");
    ERROR 1442 (HY000): Can't update table 'node' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
    mysql>
    So I thought I'd create a temporary table and then do a "INSERT INTO node SELECT * FROM node_temp;" (after copying everything including modified attributes into the node_temp table in the trigger). Unfortunately, when doing such an INSERT (bulk insert or whatever it's called), the trigger is not carried out!!! (no error messages, simply no changes in the relevant fields).

    Ughhhhhhhhhhhhrrrrrrrrrrrrr!! I've spent 4 hours on this! Any suggestions?

    Thanks in advance,

    A very frustrated GG

  2. #2
    Join Date
    Sep 2010
    Posts
    153
    1) When your after insert trigger executes, You're not storing inserted values in a variable.

    When you write a after insert trigger, make sure you declare a variable where you store data after insertion.


    2) Use that variable for further purpose.


    I will write a simple after insert trigger for you :-

    CREATE TRIGGER one_word ON tablename after insert
    as
    declare @names varchar(20);
    set @names = i.name from inserted i /*Sooner your data gets inserted in a table, vale for Name filed gets initialized in @names variable. */

    BEGIN
    IF NEW.@names REGEXP '[a-z]' THEN
    UPDATE tablename SET mwe = FALSE WHERE eid = NEW.eid;
    END IF;

    END;



    This works in MSSQL server. Kindly check in MYSql since I am yet to work in this database. I hope you get the logic

  3. #3
    Join Date
    Mar 2012
    Posts
    8
    Thank you sunny_007, but your solution doesn't work. Your syntax is not MySQL syntax.

    I suspect the problem is that you cannot insert and update on the same table because despite the AFTER keyword the transaction isn't finished during the trigger. The solution that I've found is to create two temporary tables and to reinsert the data into the non-temporary table, then to delete the temporary tables. Unfortunately, this means inserting some 300,000 tuples twice (and copying the queries into a file from a dump twice as well, I need a script to filter out garbage and turn a file into an sql file).

    Has anybody out there already encountered this problem? If so, I would certainly appreciate a tested code example that works. That would make my scripts faster as well as db population.

    Many thanks,

    GG

  4. #4
    Join Date
    Jan 2012
    Posts
    84
    A trigger cannot modify a table for which it has been fired.
    Look at the faq:
    MySQL :: MySQL 5.5 Reference Manual :: B.5 MySQL 5.5 FAQ: Triggers

    You can do this using a before insert trigger, in this way
    Code:
    DELIMITER |
    DROP TRIGGER IF EXISTS one_word |
    CREATE TRIGGER one_word BEFORE INSERT ON node 
    FOR EACH ROW
    BEGIN
    	-- one word
    	IF NEW.name REGEXP '[a-z]' THEN
    		SET NEW.mwe = FALSE;
    	END IF;
    
    END;
    |
    DELIMITER ;

  5. #5
    Join Date
    Mar 2012
    Posts
    8
    Nope, doesn't work:

    Code:
    mysql> INSERT INTO node (id, name) VALUES (111122223, "sdfds");
    ERROR 1054 (42S22): Unknown column 'name' in 'field list'
    
    mysql> SELECT id, name, one_word FROM node WHERE id=111122223;
    +-----------+-------+----------+
    | id       | name  | one_word |
    +-----------+-------+----------+
    | 111122223 | sdfds |     NULL |
    +-----------+-------+----------+
    1 row in set (0.00 sec)
    
    mysql>
    The "unknown column" problem does not come from the query, it comes from the trigger (I think!). The "id" and "name" are indeed inserted, however the "one_word" attribute remains unchanged.

    There must be a work around for this problem!
    Last edited by geeeeky.girl; 03-17-12 at 21:29.

  6. #6
    Join Date
    Jan 2012
    Posts
    84
    I cannot help because I can't see a table structure of your table and an actual trigger code.
    My test case works fine:
    Code:
    create table node(
      id int,
      name varchar(100),
      mwe boolean
    );
    
    delimiter $$
    create trigger one_word BEFORE INSERT ON node
    FOR EACH ROW
    BEGIN
        IF new.name REGEXP '[a-z]' THEN
             SET new.mwe = false;
        ELSE
             SET new.mwe = true;
        END IF;
    END;
    $$
    
    delimiter ;
    
    mysql> INSERT INTO node (id, name) VALUES (111, "s22222dfds");
    Query OK, 1 row affected (0.10 sec)
    
    mysql> INSERT INTO node (id, name) VALUES (111, '111');
    Query OK, 1 row affected (0.06 sec)
    
    mysql> select * from node;
    +------+------------+------+
    | id   | name       | mwe  |
    +------+------------+------+
    |  111 | s22222dfds |    0 |
    |  111 | 111        |    1 |
    +------+------------+------+
    2 rows in set (0.00 sec)

  7. #7
    Join Date
    Mar 2012
    Posts
    8
    Kodirko,

    Many thanks, you solved my problem, however you didn't see what was wrong with my code. Nevertheless, thanks a million, I much prefer an example that works to a lengthy (or any for that matter) explanation!

    Lesson to be learnt:
    • It has be a BEFORE INSERT ON and not an AFTER INSERT ON

    • You musn't use an UPDATE statement, but rather a SET NEW.attribute statement to update the same table


    Does anybody know how to mark a thread "SOLVED" or "RESOLVED"? The solution to this problem has been found, and I had googled it for hours. Someone might consider this little solution precious information.

    Thanks again for your patience Kordirko!

    gg

Posting Permissions

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