Bug #5892 Triggers have the wrong namespace
Submitted: 5 Oct 2004 3:08 Modified: 20 Jul 2005 17:40
Reporter: Peter Gulutzan
Status: Closed
Category:Server Severity:S1 (Critical)
Version:5.0.2-alpha-debug OS:Linux (SuSE 8.2)
Assigned to: Antony Curtis Target Version:

[5 Oct 2004 3:08] Peter Gulutzan 
Description:
The namespace of triggers is the table name. 
This is wrong. Triggers are within databases 
(schemas), so I should not have to say 
"DROP table_name . trigger_name" 
I should be able to say 
"DROP trigger_name" 

How to repeat:
mysql> create table x17 (col1 int); 
Query OK, 0 rows affected (0.28 sec) 
 
mysql> create trigger x17_bd before delete on x17 for each row begin end; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> drop trigger x17_bd; 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near '' at line 1 
mysql> drop trigger x17.x17_bd; 
Query OK, 0 rows affected (0.00 sec)
[5 Oct 2004 9:41] Victoria Reznichenko 
Verified with 5.0.2-alpha-debug-log
[1 Jun 2005 21:25] Brian Aker 
Will fix in 5.1
[2 Jul 2005 11:30] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26617
[4 Jul 2005 15:06] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26644
[5 Jul 2005 18:15] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26679
[15 Jul 2005 3:01] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27116
[19 Jul 2005 11:37] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27291
[19 Jul 2005 21:16] Konstantin Osipov 
Fixed in 5.0 tree, currently tagged 5.0.10
[19 Jul 2005 23:12] Dmitri Lenev 
Please note that now DROP TRIGGER statement does not require to specify trigger's table
explicitly. Moreover it treats first part of trigger identifier as the name of database to
which this trigger belongs. For example:
"DROP TRIGGER trg;" - drops trigger named 'trg' from the current database
"DROP TRIGGER db.trg;" - drops 'trg' trigger which belongs to 'db' database.

Another important note. When you upgrade MySQL server from version 5.0.x where x <= 9, to
version 5.0.10 (or older), you should drop all your triggers before upgrade and re-create
them after it. Otherwise you won't be able to drop your old triggers using DROP TRIGGER
statement (dropping triggers as part of dropping table or the whole database still should
work).
[20 Jul 2005 17:40] Mike Hillyer 
Documented in 5.0.10 changelog.