Bug #5892 Triggers have the wrong namespace
Submitted: 5 Oct 2004 1:08 Modified: 20 Jul 2005 15:40
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.2-alpha-debug OS:Linux (SuSE 8.2)
Assigned to: Antony Curtis CPU Architecture:Any

[5 Oct 2004 1: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 7:41] MySQL Verification Team 
Verified with 5.0.2-alpha-debug-log
[1 Jun 2005 19:25] Brian Aker 
Will fix in 5.1
[2 Jul 2005 9: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 13: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 16: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 1: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 9: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 19:16] Konstantin Osipov 
Fixed in 5.0 tree, currently tagged 5.0.10
[19 Jul 2005 21:12] Dmitry 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 15:40] Mike Hillyer 
Documented in 5.0.10 changelog.