mysql trigger in same table

Question

I am using mysql5.1 and wrote trigger after update.am using same table for insert and updae. table contains field .id,itemid,value1 all are integer..

my trigger is:

 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 excute update command i got 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.

Answer 1

As defined the trigger creates a circular reference. It inserts yet another row, which would then require the trigger to be called again, and insert yet another row, which would result in it being called again....

If SQL hadn't caught this, the vote table would have end-up being full of these (10,1,100) records, unltil running out of space for the database or on the hard disk altogether.

I'm assuming you only wish to add ONE addtional record for each 'normal' record insert. This could be achieved by modifying the logic which performs the insert itself (add the second INSERT at that level if possible). Another way would be with a separate table with its own after INSERT Trigger; the vote table's trigger would need to test for (10,1,100) values, and return (doing nothing else) in this case, and otherwise insert a record on the other table; this other table would have its trigger insert this record on table 'vote'. (Quite contrived for a relatively odd use case!)

Answer 2

Breaking circular reference will required another table e.g :

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 the tables which update each other use trigger_semaphore table in this way:

  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 now at the end of trigger

 update  trigger_semaphore set actions='--' where id=1;  

In short, the table which sets semaphore first (1) will only able to execute certain statements. You can use a simple method once you get the idea ;)