mysql 没有分隔符,MySQL分隔符语句错误

I am trying to run the following script in mysql :

DELIMITER $$$

DROP TRIGGER IF EXISTS invoice_line_insert

$$$

CREATE TRIGGER invoice_line_insert AFTER INSERT

ON invoice_line FOR EACH ROW

BEGIN

IF NEW.type = "DELIVERY" THEN

UPDATE invoice

SET invoice.etdelivery_amount = invoice.etdelivery_amount + NEW.amount

WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;

ELSE

UPDATE invoice

SET invoice.etexpense_amount = invoice.etexpense_amount + NEW.amount

WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;

END IF;

UPDATE invoice

SET invoice.vatamount = (NEW.amount * ((

SELECT vat.rate

FROM vat

WHERE vat.id_vat = NEW.vat_id_vat

) / 100)) + invoice.vatamount

WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;

UPDATE invoice

SET invoice.itamount = invoice.vatamount +

invoice.etdelivery_amount +

invoice.etexpense_amount

WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;

END

$$$

When I run it in mySql Workbench, it is working fine, but when play 2 run it authomatically (in a file called 2.sql) I get the following error :

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 $$$ DROP TRIGGER IF EXISTS invoice_line_insert $$$ CREATE TRIGGER invo' at line 1 [ERROR:1064, SQLSTATE:42000], while trying to run this SQL script:

I read on the internet that the delimiter statement is only working on specific gui, not every time. Is that true ? Why ? How to resolve it because I need the delimiter statement ?

Thanks for your help.

解决方案

This seems to be a duplicate of Play framework 2.0 evolutions and create trigger

(Note that, in my view, the better answer is the one posted by Roger on May 24 2013, i.e. the link above)

"delimiter" cannot be used in the evolution script text; I can't seem to find any documentation as to why this is so. But maybe it is got to do with the fact that "delimiter" is not an SQL statement but an SQL property.

However, there is a solution in the Evolutions section of Play 2 docs:

Play splits your .sql files into a series of semicolon-delimited statements before executing them one-by-one against the database. So if you need to use a semicolon within a statement, escape it by entering ;; instead of ;. For example, INSERT INTO punctuation(name, character) VALUES ('semicolon', ';;');.

So in your case,

Remove the "delimiter" property, and

Use ";;" instead of ";" for your inner SQL statements, so as to prevent the Play 2 parser from executing these inner SQL statements separately.

Here is an example that I have successfully tested in Play 2.3 and mysql 14.14 Distrib 5.5.40 (Ubuntu 12.04LTS):

DROP TRIGGER IF EXISTS SOFTWARE_INSERT_CT_TRIGGER;

CREATE TRIGGER SOFTWARE_INSERT_CT_TRIGGER

BEFORE INSERT ON SOFTWARE

FOR EACH ROW

BEGIN

IF NEW.CREATED_TIME = '0000-00-00 00:00:00' THEN

SET NEW.CREATED_TIME = NOW();;

END IF;;

END;

In the case of your SQL script, the following should work with Play 2.1 and above (Note that I have not tested it):

DROP TRIGGER IF EXISTS invoice_line_insert;

CREATE TRIGGER invoice_line_insert AFTER INSERT

ON invoice_line FOR EACH ROW

BEGIN

IF NEW.type = "DELIVERY" THEN

UPDATE invoice

SET invoice.etdelivery_amount = invoice.etdelivery_amount + NEW.amount

WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;;

ELSE

UPDATE invoice

SET invoice.etexpense_amount = invoice.etexpense_amount + NEW.amount

WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;;

END IF;;

UPDATE invoice

SET invoice.vatamount = (NEW.amount * ((

SELECT vat.rate

FROM vat

WHERE vat.id_vat = NEW.vat_id_vat

) / 100)) + invoice.vatamount

WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;;

UPDATE invoice

SET invoice.itamount = invoice.vatamount +

invoice.etdelivery_amount +

invoice.etexpense_amount

WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;;

END;