1

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!
0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.