MySQL delimiter statement error

Question

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.

You do not need the 2nd delimiter DROP TRIGGER IF EXISTS invoice_line_insert $$$ remove that its already there on the top. — Abhik Chakraborty
add a ; after the first statement DROP TRIGGER IF EXISTS invoice_line_insert ; — Abhik Chakraborty
@AbhikChakraborty not working either...keep trying, we'll end up finding a solution ! — Moebius

Sunil Sandhu Sunil Sandhu · Accepted Answer · 2014-12-03T21:45:20

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;

MySQL delimiter statement error

1 Answers