Mysql trigger on one table
I am using mysql5.1 and writing a trigger after update.am using the same table for insert and updae. table contains field .id, itemid, value1 are all integers ..
my trigger:
CREATE DEFINER = 'root'@'%' TRIGGER `trig_same`
AFTER UPDATE
ON `vote`
FOR EACH ROW
BEGIN
insert into vote values(10,1,100);
END|
... but when I release the update command, I got the error:
SQL Error: Can't update table 'vote' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
source to share
As defined, the trigger creates a circular link . It inserts another line, which will then require the trigger to be called again, and insert another line, which will cause it to be called again ....
If SQL hadn't caught this, the voting table would have an end filled with these (10,1 100) records, having no run for the database or hard drive at all.
I am assuming that you only want to add one additional entry for each "normal" entry. This can be achieved by changing the logic that does the insert itself (add a second INSERT at this level if possible).
EDIT [Apr 17, 2011]: As Johan pointed out, the next "fix" will fail with the same error. I'm not worried about fixing it, given a rather strange use case ...
Another way is to create a separate table with your own after the INSERT Trigger; the voting table trigger will have to test the values (10,1 100) and return (do nothing in this case) and otherwise insert the record into another table; this other table will have a trigger inserting this record into the "vote" table. (Completely contrived for a relatively odd use case!)
source to share
Eliminating the circular reference requires another table, for example:
CREATE TABLE `trigger_semaphore` (
`id` INTEGER(11) NOT NULL,
`semaphore` INTEGER(11) DEFAULT NULL,
`actions` VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
`random` INTEGER(11) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=MyISAM
CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
CREATE DEFINER = 'root'@'localhost' TRIGGER `trigger_semaphore_before_upd_tr`
BEFORE UPDATE ON `trigger_semaphore`
FOR EACH ROW
BEGIN
DECLARE semaphore int;
set New.random=RAND()*1000;
if New.actions='++' then
set New.semaphore=COALESCE(New.semaphore,0)+1;
elseif New.actions='--' then
set semaphore= COALESCE(New.semaphore,0);
if semaphore<=0 then
set New.semaphore=semaphore;
else
set New.semaphore=semaphore-1;
end if;
end if;
END;
In triggers for tables that update each other, use the trigger_semaphore table like this:
update trigger_semaphore set actions='++' where id=1;
select semaphore into _semaphore from trigger_semaphore where id=1;
if _semaphore=1 then
# ...... Here goes the code without circular reference..........
end if;
Release the semaphore at the end of startup
update trigger_semaphore set actions='--' where id=1;
In short, the table that first sets the semaphore (1) will be able to execute certain statements. You can use the simple method as soon as you get the idea;)
source to share