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