CODEWITHSUNDEEP
mysqltriggersliquibase
user2766897
user2766897

Reputation: 11

MySQL Triggers Liquibase

I have liquibase set up. I currently have 4-5 triggers I would like to load, with each trigger stored in a separate file. All of the triggers are on separate tables. However I keep getting this error:

SEVERE 9/17/13 12:05 PM:liquibase: Change Set migrations/09-16-2013-16-10.sql::129 failed. Error: Error executing SQL DELIMITER $$

DELIMITER $$
CREATE TRIGGER `UPDATE_tableA` BEFORE UPDATE on `tableA`
FOR EACH ROW BEGIN
IF (OLD.ColumnA = NEW.ColumnA) and 
  ((OLD.ColumnB is null and NEW.ColumnB is null) or (OLD.ColumnB = NEW.ColumnB)) and 
  ((OLD.ColumnB is null and NEW.ColumnB is null) or (OLD.ColumnB = NEW.ColumnB)) and 
  ((OLD.ColumnC is null and NEW.ColumnC is null) or (OLD.ColumnC = NEW.ColumnC))  
  THEN
    SET NEW.ColumnA = 0;
    SET NEW.ColumnB = NULL;
    SET NEW.ColumnB = NULL;
    SET NEW.ColumnC = NULL;
END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER `UPDATE_tableB` BEFORE UPDATE on `tableB`
FOR EACH ROW BEGIN
IF (OLD.ColumnA = NEW.ColumnA) and 
  ((OLD.ColumnB is null and NEW.ColumnB is null) or (OLD.ColumnB = NEW.ColumnB)) and 
  ((OLD.ColumnB is null and NEW.ColumnB is null) or (OLD.ColumnB = NEW.ColumnB)) and 
  ((OLD.ColumnC is null and NEW.ColumnC is null) or (OLD.ColumnC = NEW.ColumnC))  
  THEN
    SET NEW.ColumnA = 0;
    SET NEW.ColumnB = NULL;
    SET NEW.ColumnB = NULL;
    SET NEW.ColumnC = NULL;
END IF;
END$$
DELIMITER ;

Upvotes: 0

Views: 2394

Answers (2)

John Shepherd
John Shepherd

Reputation: 211

'endDelimiter' is a regExp so try this instead

<sqlFile endDelimiter="\$\$" path="..."/>

Upvotes: 1

Nathan Voxland
Nathan Voxland

Reputation: 15773

DELIMITER $$ is a command understood by the MySQL client parser, but not through JDBC that Liquibase uses.

You need to specify the delimiter in how you reference the file, perhaps as <sqlFile endDelimiter="$$" path="..."/>

Upvotes: 0

Related Questions