Bug #44084 authoring multiple triggers for different events in one script fails
Submitted: 3 Apr 2009 20:23 Modified: 3 Apr 2009 20:29
Reporter: Dave Nechodom Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.1.31-community OS:Microsoft Windows (XP SP2)
Assigned to: CPU Architecture:Any
Tags: multiple, trigger

[3 Apr 2009 20:23] Dave Nechodom
Description:
Attempting to author three triggers on one table for three events, before insert, after insert, and after update. Result is Error 1235:
"Script line: 2	This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'".

How to repeat:
"CREATE" script:
delimiter $$
create trigger bi_tb_keys before insert on tb_keys for each row begin
  if new.enabled = 0 then
    set new.enabled = 1;
  end if;
end $$

create trigger ai_tb_keys after insert on tb_keys for each row begin
  insert into tb_keys_change (key_id, keyword, action, change_by, change_ts)
  values (new.key_id, new.keyword, new.enabled, 'insert', new.change_by, now());
end $$

create trigger au_tb_keys after update on tb_keys for each row begin
  declare act varchar(10);

  if new.enabled then
    set act = 'enabled';
  else
    set act = 'disabled';
  end if

  insert into tb_keys_change (key_id, keyword, action, change_by, change_ts)
  values (new.key_id, new.keyword, new.enabled, act, new.change_by, now());
end $$

delimiter ;

End "CREATE" script:

Suggested fix:
Correctly identify events for triggers.
[3 Apr 2009 20:29] Dave Nechodom
Argh! This was caused by interim script errors corrected on the fly.