Organizational Research By

Surprising Reserch Topic

mysql triggers cannot update rows in same table the trigger is assigned to sugg

mysql triggers cannot update rows in same table the trigger is assigned to sugg  using -'mysql,database,database-design,stored-procedures,triggers'

MySQL doesn't currently support updating rows in the same table the trigger is assigned to since the call could become recursive. Does anyone have suggestions on a good workaround/alternative? Right now my plan is to call a stored procedure that performs the logic I really wanted in a trigger, but I'd love to hear how others have gotten around this limitation.

Edit: A little more background as requested. I have a table that stores product attribute assignments. When a new parent product record is inserted, I'd like the trigger to perform a corresponding insert in the same table for each child record. This denormalization is necessary for performance. MySQL doesn't support this and throws:

Can't update table 'mytable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.  A long discussion on the issue on the MySQL forums basically lead to: Use a stored proc, which is what I went with for now.

Thanks in advance!

asked Oct 7, 2015 by sujata naik
0 votes

Related Hot Questions

3 Answers

0 votes

You can actually up the rows in the same table as the trigger. The thread you linked to even has the solution.

For example:

TestTable ( id / lastmodified / random )

create trigger insert_lastmod
before insert on TestTable
for each row
set NEW.lastmodified = NOW();

insert into TestTable ( `random` ) values ( 'Random' );

select * from TestTable;
| id | lastmodified        | random              |
|  1 | 2010-12-22 14:15:23 | Random              |
answered Oct 7, 2015 by rajeevr642
0 votes

You can actually do that The below is an example for same

create trigger test2 
before insert on ptrt 
for each row 
if NEW.DType = "A" then 
set NEW.PA = 500; 

elseif NEW.DType = "B" then 
set NEW.PA = 1000; 

set NEW.PA = 0; 

answered Oct 7, 2015 by amit.gupta
0 votes

I suppose you could call the stored proc in your trigger. HOwever, if you want to update some fields in the same records that you are changing (such as an updatedby or lastupdated column) then you can do this in a beofre trigger according to the refernce manual.

This is a common operation for triggers and I find it difficult to believe it isn't supported.

answered Oct 7, 2015 by manju bhargava