Using liquibase to create triggers in mysql

Question

I want to create a simple trigger in mysql using liquibase. The following script works directly from mysql:

delimiter $$

CREATE TRIGGER myTrigger
    BEFORE INSERT ON myTable FOR EACH ROW
BEGIN
    IF(NEW.my_timestamp IS NULL) THEN
        SET NEW.my_timestamp = now();
    END IF;
END$$

delimiter ;

So, I want to create a changeset for liquibase to use that can apply this trigger using the update command, and will also create a suitable sql script when using the updateSQL command.

I have tried a variety of options in the changeset including splitStatements and endDelimiter, but have only been able to get something that works either with the update command or with the updateSQL command. Not with both.

here's a sample change set using formatted sql which works fine when I use the update command, but does not create suitable sql when I use the updateSQL command

-- liquibase formatted sql
-- changeset pcoates33:trigger-1 splitStatements:false

CREATE TRIGGER myTrigger
    BEFORE INSERT ON myTable FOR EACH ROW
BEGIN
    IF(NEW.my_timestamp IS NULL) THEN
        SET NEW.my_timestamp = now();
    END IF;
END

-- rollback DROP TRIGGER IF EXISTS myTrigger;

and here's one that works how I want for updateSQL, but fails for update:

-- liquibase formatted sql
-- changeset pcoates33:trigger-1 splitStatements:false

delimiter $$

CREATE TRIGGER myTrigger
    BEFORE INSERT ON myTable FOR EACH ROW
BEGIN
    IF(NEW.my_timestamp IS NULL) THEN
        SET NEW.my_timestamp = now();
    END IF;
END$$

delimiter ;

-- rollback DROP TRIGGER IF EXISTS myTrigger;

The basic problem is

1. The mysql script needs both the delimiter $$ and delimiter ; in it
2. The jdbc call made by liquibase will fail if it has the delimiter $$ at the start

Answer 1

Your trigger may be easily converted to single-statement form:

CREATE TRIGGER myTrigger
BEFORE INSERT 
ON myTable 
FOR EACH ROW
SET NEW.my_timestamp = COALESCE(NEW.my_timestamp, NOW());

which does not need in delimiter re-assign.

---

If NULL is inserted into this column because this column is not listed in columns list of INSERT query at all then you may simply use DEFAULT CURRENT_TIMESTAMP in the column definition, and trigger not needed. But if NULL value is set explicitly than this method is not applicable.

Answer 2

I agree with @Akina, that a good solution is either not to use triggers, or condense them into single statements.

I am trying to introduce liquibase to an existing database, so wanted to be able to keep it the same initially. Then apply changes to make it simpler.

So, the basic problem is

1. The mysql script needs the delimiter $$ and delimiter ; in it
2. The jdbc call made by liquibase will fail if it has the delimiter $$ at the start

After numerous attempts the solution I came up with relied on using the gradle plugin to run the liquibase updateSql command. I couldn't acheive it with liquibase on it's own. I basically comment the parts of code that liquibase does not recognise, then post process the script file created by liquibase to uncomment the statements.

Here's my changeset as formatted sql:

-- liquibase formatted sql
-- changeset pcoates33:trigger-1 splitStatements:false stripComments:false
-- delimiter $$

CREATE TRIGGER myTrigger
    BEFORE INSERT ON myTable FOR EACH ROW
BEGIN
    IF(NEW.my_timestamp IS NULL) THEN
        SET NEW.my_timestamp = now();
    END IF;
END-- $$

-- delimiter ;
-- rollback DROP TRIGGER IF EXISTS myTrigger;

I can run liquibase update with that and it is correctly applied.

I configured gradle to use the liquibase plugin, and also to use outputFile : "$projectDir/update.sql"

Then updated the build.gradle file to extend the updateSql task that was added by the plugin to change the comments from the statements I want in the output

updateSQL{
  doLast {
    ant.replace(file: "$projectDir/update.sql", token: '-- delimiter', value: 'delimiter')
    ant.replace(file: "$projectDir/update.sql", token: '-- $$', value: '$$')
  }
}