triggers causing syntax errors

GoogleCodeExporter commented 9 years ago

I need to create a trigger on a table.  I have the following script.

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
  END;
|

delimiter ;
--//@UNDO

drop trigger testref;

When I run this, I get the following errors:

Applying #226701: 226701_user_notify_kitchen_journal_trigger.sql...
Failed to apply changes: java.lang.RuntimeException: 
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'delimiter |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INS' at line 1
java.lang.RuntimeException: 
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'delimiter |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INS' at line 1
    at com.dbdeploy.appliers.DirectToDbApplier.applyChangeScriptContent(DirectToDbApplier.java:50)
    at com.dbdeploy.appliers.DirectToDbApplier.apply(DirectToDbApplier.java:29)
    at com.dbdeploy.Controller.processChangeScripts(Controller.java:40)
    at com.dbdeploy.DbDeploy.go(DbDeploy.java:111)
    at com.dbdeploy.CommandLineTarget.main(CommandLineTarget.java:13)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'delimiter |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INS' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.Util.getInstance(Util.java:384)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2512)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:781)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:624)
    at com.dbdeploy.database.changelog.QueryExecuter.execute(QueryExecuter.java:22)
    at com.dbdeploy.appliers.DirectToDbApplier.applyChangeScriptContent(DirectToDbApplier.java:47)
    ... 4 more

Original issue reported on code.google.com by visseraj on 19 Nov 2010 at 2:04

GoogleCodeExporter commented 9 years ago

Hey

I've got the same error,and i fixed it by setting delimiter in build.xml
I've added param delimiter="|" to <dbdeploy> tag, so it looks like this in my 
project:
<dbdeploy driver="${db.driver}" url="${db.url}"
    userid="${db.username}" password="${db.password}"
    dir="." delimiter="|" />

Then remove "DELIMITER" lines from your sql script and it should work.

Greetings.

Original comment by ert...@gmail.com on 29 Jan 2011 at 1:50

GoogleCodeExporter commented 9 years ago

[deleted comment]

GoogleCodeExporter commented 9 years ago

can you please briefly explain wat you ve done for that error to disappear?I 
dono where is build.xml file?

Original comment by sathiy...@gmail.com on 17 Feb 2011 at 2:39

kimptoc / dbdeploy

triggers causing syntax errors #50