Bug #53023 DML statement for drop trigger gets removed automaticly
Submitted: 21 Apr 2010 12:44 Modified: 27 May 2010 16:50
Reporter: Michael Schramm Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:5.2.19 OSS, Revision 5680 OS:Windows (Windows 7)
Assigned to: Alexander Musienko CPU Architecture:Any
Tags: drop trigger removed, trigger

[21 Apr 2010 12:44] Michael Schramm
Description:
If you doubleclick on a table and choose triggers and write a trigger

-- Trigger DDL Statements
USE `usr_web5_3`;
DELIMITER $$

DROP TRIGGER trg_au_sometrigger$$ 

CREATE TRIGGER trg_au_sometrigger AFTER UPDATE ON some_table 
...

the DROP TRIGGER statement gets removed automaticly!

How to repeat:
Open the EER Model
open a table in it
select trigger window for this table

paste in the SQL below (USE and DELIMITER $$ is predefined while opening the window) afterwards if you move the cursor the Drop Statement will be removed:
------------------------------------------------------------------------

DROP TRIGGER trg_au_some_trigger$$

CREATE TRIGGER trg_au_some_trigger AFTER UPDATE ON some_table 
FOR EACH ROW

BEGIN

END$$
[21 Apr 2010 13:49] Valeriy Kravchuk
Thank you for the problem report. Please, check with a newer version, 5.2.19, and inform about the results.
[22 Apr 2010 22:04] Michael Schramm
Thiss no longer occured in 5.2.19 beta
[26 Apr 2010 9:10] Michael Schramm
Sorry my last comment was wrong i actually found this behavier in the
version 5.2.19 OSS, Revision 5680 also.

Problem with removed DROP statements still exists.
[26 Apr 2010 17:42] Sveta Smirnova
Thank you for the feedback.

Verified as described: after writing DROP TRIGGER, switch to another table, then switch back and see no DROP statement
[27 Apr 2010 1:47] Johannes Taxacher
the drop statement is removed because WB does insert drop statements for all objects if set in export/fwd eng. wizard.
is this behavior non-desirable in your case?
[27 Apr 2010 13:03] Michael Schramm
You are right, if the WB inserts DROP statement while exporting or forw. eng. the remove of drop statements inside the trigger SQL is usefull.

BUT
If i choose "DROP All Objects Before CREATE Object" while forward eng. the
error occured that only one trigger can be used for an event.
-> The old trigger was defenitly not removed.

So it seems to me that NOT the remove of DROP statements inside the trigger SQL is false but rather the "DROP All Objects Before CREATE Object" don't work proper for triggers.

I just tested it again and the generated SQL code contains no DROP statement for the Trigger
----------

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `testdatabase` ;

DELIMITER $$
USE `testdatabase`$$

CREATE TRIGGER `trg_au_test` AFTER UPDATE ON testtable
FOR EACH ROW
BEGIN
...
[26 May 2010 21:07] Johannes Taxacher
fix confirmed in repository
[27 May 2010 16:50] Tony Bedford
An entry has been added to the 5.2.22 changelog:

In the Triggers tab of the Table Editor, if a trigger was written that contained a DROP TRIGGER statement, the statement was automatically removed.