MySQL triggers cannot update rows in the same table to which the trigger is assigned. Proposed Solution?

Question

MySQL currently does not support updating rows in the same table that the trigger is assigned to, because the call may become recursive. Does anyone have any suggestions for a good workaround / alternative? Right now, my plan is to invoke a stored procedure that executes the logic that I really need in the trigger, but I would like to hear others bypass this restriction.

Edit: A little more background on request. I have a table that stores product attribute assignments. When a new parent product record is inserted, I would like the trigger to perform the corresponding insert in the same table for each child record. This denormalization is necessary for performance. MySQL does not 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 MySQL forums mainly results in: Using the stored procedure I went with so far.

Thanks in advance!

Answer 1

, . , , .

:

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              |
+----+---------------------+---------------------+

Answer 2

, . , , (, ), beofre refernce. http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

, , .

Answer 3

, , .

Answer 4

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

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

else 
set NEW.PA = 0; 
END IF; 
END;$$

DELIMITER;

Answer 5

: D

. /.

BEGIN
 SET NEW.DateTimeUpdated = NOW();
END