I'm trying to create triggers in MySQL using the following code. The triggers are meant to validate device authorization before inserts or updates.

Here's the snippet of the code I'm using:

DELIMITER $$

CREATE TRIGGER trg_buckets_before_insert
BEFORE INSERT ON Buckets
FOR EACH ROW
BEGIN
    DECLARE auth_status TINYINT;
    SELECT is_authorized INTO auth_status 
    FROM Devices 
    WHERE device_id = @CURRENT_DEVICE;

    IF auth_status = 0 OR auth_status IS NULL THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Device not authorized';
    END IF;
END$$

DELIMITER ;

However, when I execute the code, I get the following errors:

Error Code: 1064. 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 'END$$ DELIMITER ;' at line X

I've also encountered issues with the DECLARE statements and SIGNAL commands, with errors like:

Error executing statement: DECLARE auth_status TINYINT Error executing statement: END$$ Error: 1064 (42000): You have an error in your SQL syntax.

I am using MySQL version 8.0.29, and I've tested running the statements through DBeaver.

What could be causing these syntax errors, and how can I properly create triggers without this issue?

I'm trying to create triggers in MySQL using the following code. The triggers are meant to validate device authorization before inserts or updates.

Here's the snippet of the code I'm using:

DELIMITER $$

CREATE TRIGGER trg_buckets_before_insert
BEFORE INSERT ON Buckets
FOR EACH ROW
BEGIN
    DECLARE auth_status TINYINT;
    SELECT is_authorized INTO auth_status 
    FROM Devices 
    WHERE device_id = @CURRENT_DEVICE;

    IF auth_status = 0 OR auth_status IS NULL THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Device not authorized';
    END IF;
END$$

DELIMITER ;

However, when I execute the code, I get the following errors:

Error Code: 1064. 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 'END$$ DELIMITER ;' at line X

I've also encountered issues with the DECLARE statements and SIGNAL commands, with errors like:

Error executing statement: DECLARE auth_status TINYINT Error executing statement: END$$ Error: 1064 (42000): You have an error in your SQL syntax.

I am using MySQL version 8.0.29, and I've tested running the statements through DBeaver.

What could be causing these syntax errors, and how can I properly create triggers without this issue?

• sql
• mysql
• triggers

• The code is correct itself. dbfiddle.uk/hya4xh_0 Looks like your client/connector/framework issue. and I've tested running the statements through DBeaver Use CLI. – Akina Commented Feb 5 at 5:59

1 Answer 1

DELIMITER is an artifact command used by some client software (mysql CLI) to give you precise control over query parsing, when you're not sure the client will be able to distinguish a statement-ending ; from a middle-of-statement ;.
However nowadays most parsers are able to detect the statement-ending ; by theirselves (as you can see in a small example where it correctly sees two statements, without having to set the DELIMITER).

DBeaver has this ability and understands DELIMITER for compatibility reasons; but it's perfectly able to work without DELIMITER, and has a preferences to let you choose to ignore DELIMITER or interpret it in its historical way, as mysql CLI would.

You perhaps unintentionally clicked on Window -> Preferences -> Editors -> SQL Editor -> SQL Processing -> Delimiters -> Ignore native delimiter or set […] -> Blank line is statement delimiter to Always or Never.
Try to reset those settings to Not ignore native delimiter and Smart.