Pentaho Data Integration: ‘Execute SQL statement’ to create trigger

pentaho

I'm trying to create a mysql trigger via an Execute SQL Statement step in my transformation. Below are the commands I'm attempting to run.

DROP TRIGGER IF EXISTS standing_actions_swap_before_insert_row_tr;
DELIMITER $$
CREATE DEFINER=`user`@`%` TRIGGER standing_actions_swap_before_insert_row_tr BEFORE INSERT ON `standing_actions_swap`
FOR EACH ROW 
BEGIN 
    SET NEW.id = CONCAT(NEW.acad_career, NEW.status, NEW.action); 
END
$$
DELIMITER ;

Unfortunately, for whatever reason, while the following works in various other db software suites (e.g. Sequel Pro, Aqua Data Studio), I always get an error in Pentaho Data Integration. I've tried omitting delimiters and checking and unchecking the Execute as a Single Statement box – unfortunately, it still no worky.

Any help is appreciated. Thanks!


Edit:

Here's is the stack trace:

2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : An error occurred, processing will be stopped: 
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - Couldn't execute SQL: DELIMITER $$
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - CREATE DEFINER=`user`@`%` TRIGGER standing_actions_swap_before_insert_row_tr BEFORE INSERT ON `standing_actions_swap`
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - FOR EACH ROW 
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - BEGIN 
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 -     SET NEW.id = CONCAT(NEW.acad_career, NEW.status, NEW.action)
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - 
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - 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 $$
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - CREATE DEFINER=`user`@`%` TRIGGER standing_actions_swap_before_inse' at line 1
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Error initializing step [Recreate ac.student_actions_swap before_insert trigger]
2015/05/15 16:13:13 - recreate_standing_actions_swap_trigger - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Step [Recreate ac.student_actions_swap before_insert trigger.0] failed to initialize!
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - Finished reading query, closing connection.
2015/05/15 16:13:13 - Spoon - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : recreate_standing_actions_swap_trigger: preparing transformation execution failed
2015/05/15 16:13:13 - Spoon - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : org.pentaho.di.core.exception.KettleException: 
2015/05/15 16:13:13 - Spoon - We failed to initialize at least one step.  Execution can not begin!
2015/05/15 16:13:13 - Spoon - 
2015/05/15 16:13:13 - Spoon - 
2015/05/15 16:13:13 - Spoon -   at org.pentaho.di.trans.Trans.prepareExecution(Trans.java:1149)
2015/05/15 16:13:13 - Spoon -   at org.pentaho.di.ui.spoon.trans.TransGraph$27.run(TransGraph.java:3989)
2015/05/15 16:13:13 - Spoon -   at java.lang.Thread.run(Thread.java:695)
2015/05/15 16:13:13 - recreate_standing_actions_swap_trigger - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Errors detected!
2015/05/15 16:13:13 - recreate_standing_actions_swap_trigger - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Errors detected!

Best Answer

You can omit the DELIMITER commands and enable the option "Send SQL as single statement":

CREATE FUNCTION Tes()
BEGIN
  SELECT * FROM actors;
END