MYSQL Trigger works if I use 'after update' but not 'after insert'?

Question

I've written a trigger to insert a row into table b after an operation on table a. For some reason, it has no effect if I add this trigger 'after insert' and then insert a row. However, it does work if I add the trigger as 'after update', and update the row.

Here's the trigger code. When I replace 'AFTER UPDATE' with 'AFTER INSERT', and do an insert, then nothing happens when i insert a new row. I get no errors when creating the trigger, and I am not trying to update the same table the trigger is being set upon. Any help is appreciated! thanks, Jen

drop trigger if exists insertUndecided;  
DELIMITER //  
CREATE TRIGGER insertUndecided  
     AFTER UPDATE ON jiraissue
     FOR EACH ROW
     BEGIN
        insert into nodeassociation (SOURCE_NODE_ID, SOURCE_NODE_ENTITY, SINK_NODE_ID, SINK_NODE_ENTITY, ASSOCIATION_TYPE, SEQUENCE)
   select
      NEW.id as SOURCE_NODE_ID,
      'Issue' as SOURCE_NODE_ENTITY,
      (select pv.id from projectversion pv
         where pv.vname='undecided'
         and pv.project=NEW.project ) as SINK_NODE_ID,
      'Version' as SINK_NODE_ENTITY,
      'IssueFixVersion' as ASSOCIATION_TYPE,
      NULL as SEQUENCE

   from dual  where exists
      (select pkey from jiraissue
         where id=NEW.id and id not in
            (select distinct source_node_id from nodeassociation
               where source_node_entity='Issue' and SINK_NODE_ENTITY='Version'
               and ASSOCIATION_TYPE='IssueFixVersion') );

 END;//

DELIMITER ;

See this answer that seems to apply to your situation as well: stackoverflow.com/a/9097459/870122 — perissf
– perissf, Commented Feb 10, 2012 at 8:08
Try to reproduce this behavior without trigger. Execute SELECT statement. Will it return any rows? — Devart
– Devart, Commented Feb 10, 2012 at 8:23
@perissf - thanks for your reply. that issue is regarding "Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger." While I am indeed reading the jiraissue table, I am not updating it. And that does not explain why it seems to work if i run it 'AFTER UPDATE', but not 'AFTER INSERT'? — jen
– jen, Commented Feb 10, 2012 at 17:28
@Devart - thanks for your reply. Yes, I checked the select statements at the mysql prompt by replacing NEW.id and NEW.project with the newly added id and project. they return expected results. — jen
– jen, Commented Feb 10, 2012 at 17:38
Has the jiraissue.id field the AUTO_INCREMENT attribute ? If so you may be running into something like NEW.id = 0 problem , but I have no specific arguments on this. — georgepsarakis
– georgepsarakis, Commented Mar 3, 2012 at 8:54

Michel Feldheim · Accepted Answer · 2012-11-08 09:57:56Z

On MySQL 5.5.20 with InnoDB tables i've reproduced your situation in a simplified test. both, inserting using a result from the trigger table which is referring to the new row alreay and directly inserting using the NEW values worked fine

CREATE TABLE test1(a1 INT NOT NULL auto_increment, b1 INT, PRIMARY KEY (a1) );
CREATE TABLE test2(a1 INT, b1 INT);
CREATE TABLE test3(a1 INT, b1 INT);

DELIMITER ;;

CREATE TRIGGER testAI AFTER INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a1 = NEW.a1, b1 = NEW.b1;
    INSERT INTO test3 ( a1, b1 ) SELECT a1, b1 FROM test1 WHERE a1 = NEW.a1;
  END;
;;

DELIMITER ;

Fire the trigger

INSERT INTO test1 (b1) VALUES (1),(2),(3);

Directly inserted from NEW values

mysql> SELECT * FROM test2;
+------+------+
| a1   | b1   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

Inserted from SELECT result

mysql> SELECT * FROM test3;
+------+------+
| a1   | b1   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.5.20    |
+-----------+
1 row in set (0.00 sec)

So this works for both auto_increment and explicitly inserted values. Your problem must be somewhere else

Collectives™ on Stack Overflow

MYSQL Trigger works if I use 'after update' but not 'after insert'?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related