Playback Framework 2.0 and Create a Trigger

Question

I am trying to get this trigger created using Play 2.0.3. It works fine in MySQL if I run it manually, but it does not work when trying to start it from Play.

delimiter | create trigger company_updated before update on company for each row begin set new.updated = CURRENT_TIMESTAMP; end; | delimiter ; 

The error that it throws is this:

We got the following error: you have an error in the SQL syntax; check the manual for your version of MySQL server for the correct syntax to use next to the delimiter | create a company_updated trigger before updating the company for each row 'on line 1 [ERROR: 1064, SQLSTATE: 42000] when trying to run this SQL script:

Answer 1

The delimiter keyword is not an SQL statement; it is used only in the mysql command-line client.

Try removing parts of delimiter :

 create trigger company_updated before update on company for each row begin set new.updated = CURRENT_TIMESTAMP; end; 

But, as you already mentioned, you can hit the “Playback Error” in the semi-colony :(

Answer 2

This was a mistake and was fixed in 2.1. You can use double semicolons. This is described here: http://www.playframework.com/documentation/2.1.0/Evolutions

So your code should be modified as follows:

 create trigger company_updated before update on company for each row begin set new.updated = CURRENT_TIMESTAMP;; end; 

Answer 3

This seems to be a limitation that we cannot work with. So, at this time, we simply do not support triggers in our application. Instead, we simply redefine the methods for saving and updating our models ...

 @Override public void save() { this.updated = new Date(); this.created = this.updated; super.save(); } @Override public void update() { this.updated = new Date(); super.update(); } 

Answer 4

No separator instruction needed. Play will split your file into multiple requests, one file ends with ";" character.

If you want to create a complex trigger, you do not want the playback to crash your file after a half-toll, so you avoid it with the double semicolon ";;".

You end each request with a single semicolon and inside the trigger you end the request with a double semicolon.

Example:

 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 )) + 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;