I’m using the following SQL Script in my IDE DBeaver, MySQL 8.0.21 Linux (Docker Container). Database is in utf8mb4 / utf8mb4_general_ci encodings.
DELIMITER //
CREATE OR REPLACE TRIGGER trg_line_total
BEFORE INSERT ON LINE
FOR EACH ROW
BEGIN
SET NEW.LINE_TOTAL = NEW.LINE_UNITS * NEW.LINE_PRICE;
END //
DELIMITER ;
It seems to be valid SQL, but it is returning the following error, as if it wasn’t reading the full line. I can remove tabs, line returns and it will read more or less characters.
Error occurred during SQL script execution
Reason:
SQL Error [1064] [42000]: 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 'TRIGGER trg_line_total
BEFORE INSERT ON LINE
FOR EACH ROW
BEGIN
SET NEW.LINE_TO' at line 1
I already seemingly fixed this error once today by deleting everything I had in my editor and typing it out from scratch, as if there was some invisible line ending that was messing things up or getting interpreted. I looked in vim and used the :set list command and I see are regular line return carriages. What could it be?
Advertisement
Answer
Unfortunately, there’s no ALTER TRIGGER or CREATE OR REPLACE TRIGGER in Oracle’s MySQL. It supports only Create Trigger format.
For MariaDB, in version 10.1.4, added support CREATE OR REPLACE TRIGGER to their drop-in replacement for MySQL.
If you are sure that trigger doesn’t exists
Use CREATE instead of CREATE OR REPLACE.
If you are modifying existing trigger
I would suggest that the best practice is to lock the table where the trigger lives, so no rows are impacted with the trigger absent. Dropping and adding triggers while a table is locked is allowed.
mysql> LOCK TABLES t1 WRITE; -- the next prompt appears once you've obtained the lock
mysql> DROP TRIGGER t1_bi;
mysql> DELIMITER $$
mysql> CREATE TRIGGER ti_bi BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
...
END $$
mysql> DELIMITER ;
mysql> UNLOCK TABLES;
Reference: Modify Existing Trigger Definition in MySQL