What syntax to use for triggers - won't create them without manual editing
Posted by:
Brian Sims
Date: December 08, 2008 10:52AM
I've had this problem in version 5.0.26 through 5.0.28 of workbench SE. When I export out the forward create scripts, the sql will not create my triggers. If I copy and paste from that script just the trigger create statements, it works fine. Here's what I'm doing
I have a table created in the workbench named wo_line. The trigger tab contains the following lines (copy and pasted out of the table's trigger tab - between the ------- lines...)
-----------------------------------------
-- Trigger DDL Statements
USE `NNerp`;
DELIMITER //
create trigger wo_line_insert after insert on wo_line
for each row CALL wo_lineactive('I',NEW.workorder,NEW.workorderline,'',NEW.wostatus);
create trigger wo_line_update after update on wo_line
for each row call wo_lineactive('U',NEW.workorder,NEW.workorderline,old.wostatus,new.wostatus);
create trigger wo_line_delete after delete on wo_line
for each row call wo_lineactive('D',OLD.workorder,OLD.workorderline,old.wostatus,'');
//
-----------------------------------------
I then go to file, export, forward create sql script and check the generate insert statements, browse to the file I want to replace, next, next, finish.
I then go into mysql query browser (version 1.2.12), log into the server and drop the database I'm creating (NNerp), then new, script tab and open the create script made by the workbench and execute the create script. I get the following error and the trigger does NOT exist in information_schema.triggers:
Script line: 1835 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 'create trigger wo_line_update after update on wo_line
for each row call wo_l' at line 4
so if I go look at the script, here are the lines around #1835
1834 DELIMITER //
1835 create trigger wo_line_insert after insert on wo_line
1836 for each row CALL wo_lineactive('I',NEW.workorder,NEW.workorderline,'',NEW.wostatus);
1837
1838 create trigger wo_line_update after update on wo_line
1839 for each row call wo_lineactive('U',NEW.workorder,NEW.workorderline,old.wostatus,new.wostatus);
1840
1841 create trigger wo_line_delete after delete on wo_line
1842 for each row call wo_lineactive('D',OLD.workorder,OLD.workorderline,old.wostatus,'');//
1843
1844
1845 DELIMITER ;
If I copy lines 1835 to 1844 into a new script tab and execute them, the triggers ARE created correctly. How am I supposed to enter the triggers into the workbench so the triggers are created without manual editing of the create script?