Error when creating trigger

[DanielBailey-web]

Describe the bug I get the following error when running the code below:

CREATE TRIGGER `before_emails_update` BEFORE UPDATE ON `emails` FOR' at line 1

To Reproduce I assume the problem would work with any trigger that you try to create.

Expected behavior Trigger should be created

Code

DROP TRIGGER IF EXISTS `before_emails_update`;
DELIMITER $$
CREATE TRIGGER `before_emails_update` BEFORE UPDATE ON `emails` FOR EACH ROW
BEGIN
    IF (NEW.viewCount = -1) THEN
        SET NEW.viewCount = OLD.viewCount + 1;
        SET NEW.lastViewed = NOW();
        if (OLD.viewCount = 0) THEN
            SET NEW.firstViewed = NOW();
        END IF;
    END IF;
END$$
DELIMITER ;

Desktop (please complete the following information):

• SQLTools Version [0.6]
• VS Code Version: [latest]
• OS: [Mac M1]
• Driver:
  • [ ] PostgreSQL/Redshift
  • [x] MySQL/MariaDB
  • [ ] MSSQL/Azure
  • [ ] SQLite
  • [ ] Other? Which...
• Database version: [MySQL v5.6]

[gjsjohnmurray]

@DanielBailey-web the 0.6.0 version of the MySQL driver which it looks like you're using was published only yesterday. Have you previously been able to run this kind of SQL code against your MySQL database using an earlier version of the driver? Or are you using SQLTools this way for the first time?

A potential way to determine if the problem is specific to 0.6.0 is to use the gearwheel menu alongside the MySQL driver extension in VS Code's Extensions view, then choose "Install Another Version...", then pick 0.5.1. You'll be prompted to reload VS Code. Then you can test the same statements to see if it's an old problem or a new one.

[DanielBailey-web]

I had tried previously and it did not work with 0.5.1. I had commented on the PR #1140 (albeit on a different GitHub account @vulcantech-io) for 0.5.2 asking if that would fix it. But no one got back to me on it 😬

[viccie30]

The same issue also exists for SQLite, which uses the same/similar syntax for trigger definitions: https://www.sqlite.org/lang_createtrigger.html. Every trigger definition contains embedded semicolons.

Another SQL related project ran into the same issues some years ago, if I understand this issue and the related PR correctly.

[DanielBailey-web]

@gjsjohnmurray is there any movement on this? This is quite literally a feature I would use daily.

[gjsjohnmurray]

@DanielBailey-web I am not aware of anyone actively working on this at the moment.