Bug #19048 Renaming a table looses TRIGGERs
Submitted: 12 Apr 2006 14:27 Modified: 12 Apr 2006 15:09
Reporter: Bertin Colpron
Status: Duplicate
Category:Server Severity:S3 (Non-critical)
Version:5.0.15 OS:Microsoft Windows (WinXP)
Assigned to: Target Version:

[12 Apr 2006 14:27] Bertin Colpron 
Description:
If I rename a table with one or more TRIGGERs associated, then

- The triggers does not fire for the renamed table.
- The associated triggers are no longer listed by SHOW TRIGGERS
- The triggers cannot be dropped (associated table does not exist).
- Cannot recreate trigger with the same names (trigger already exists).

Note: If I rename the table back to its previous name, the triggers show up normally.

How to repeat:
CREATE TABLE t1 (c VARCHAR(100));

CREATE TRIGGER my_trigger BEFORE INSERT ON t1
FOR EACH ROW SET @a = @a + 1;

ALTER TABLE t1 RENAME TO t2;

SHOW TRIGGERS;       // Empty set

CREATE TRIGGER my_trigger BEFORE INSERT ON t2
FOR EACH ROW SET @a = @a + 1;
         // Fails. Trigger 'my_trigger' already exists

DROP TRIGGER my_trigger;  // Fails. Table t1 doesn't exist
[12 Apr 2006 14:56] Miguel Solorzano 
Thank you for the bug report. Duplicate of bug:
http://bugs.mysql.com/bug.php?id=13525
[12 Apr 2006 15:09] Bertin Colpron 
Sorry about that. I just noticed that the bug search page looks only for 'Active' bugs by
default...