Bug #47315 Incorrect ERROR 1235 when trying to create a trigger with a name that exists

• Submitted: 15 Sep 2009 4:28
• Modified: 15 Mar 2011 16:39
• Reporter: Roel Van de Paar
• Status: Not a Bug
• Category: MySQL Server: Errors
• Severity: S2 (Serious)
• Version: 5.1.37, 5.1.40sp1
• OS: Any
• Assigned to: Davi Arnaut
• CPU Architecture: Any

[15 Sep 2009 4:28] Roel Van de Paar

Description:
---------
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.20 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

mysql> CREATE TRIGGER ins_sum BEFORE UPDATE ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
ERROR 1359 (HY000): Trigger already exists
---------

Third line should say:
ERROR 1359 (HY000): Trigger 'ins_sum' already exists

How to repeat:
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;

Suggested fix:
Fix error

[15 Sep 2009 4:31] Roel Van de Paar

Verifying as D4

Likely error 1235 takes some sort of precedence over 1359, but error 1359 is the one that should be shown here.

[15 Mar 2011 16:39] Davi Arnaut

There is no requirement for a predetermined order under which conditions should be checked, errors are issued whenever relevant.