Início / dba / Perguntas / 343362
Accepted
TimLer
Asked: 2024-11-01 23:00:15 +0800 CST

MariaDB Criar um gatilho não funciona

  • 772

Tenho um MariaDB 10.6 Server em um Rhel linux e quero criar um gatilho para uma tabela. Este é meu código:

DELIMITER //

CREATE TRIGGER before_salary_update
BEFORE UPDATE ON Employee
FOR EACH ROW
BEGIN
    DECLARE salary_change DECIMAL(10, 2);
    
    -- Calculate the percentage change in salary
    SET salary_change = (NEW.salary - OLD.salary) / OLD.salary * 100;

    -- Check if the change is within the 10% threshold
    IF salary_change > 10 OR salary_change < -10 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Error: Salary change exceeds 10% limit.';
    END IF;
END //

DELIMITER ;

Quando executo meu código com o delimitador, meu terminal congela e tenho que interrompê-lo com ctrl+c. Quando executo sem o delimitador, recebo este erro:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

Também tentei copiar isso para um arquivo sql e executá-lo do SO. Mas tenho o mesmo problema.

mariadb

1 respostas

  1. Best Answer

    Verifique qual é o delimitador atual usado no terminal usando\s

    MariaDB [crm_gesti]> \s
--------------
mysql  Ver 15.1 Distrib 10.6.15-MariaDB, for Linux (x86_64) using  EditLine wrapper

Connection id:          3028472
Current database:       crm_gesti
Current user:           root@localhost
SSL:                    Not in use
Current pager:          less
Using outfile:          ''
Using delimiter:        ;   #########  --- > see here
Server:                 MariaDB
Server version:         10.6.15-MariaDB MariaDB package
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb3
Conn.  characterset:    utf8mb3
UNIX socket:            /run/mysql/mysql.sock
Uptime:                 128 days 18 hours 17 min 22 sec

    De acordo com seu código, funciona bem

    MariaDB [crm_gesti]> CREATE TABLE Employee (
    ->   salary int);
Query OK, 0 rows affected (0.013 sec)

MariaDB [crm_gesti]>
MariaDB [crm_gesti]>
MariaDB [crm_gesti]> DELIMITER //
MariaDB [crm_gesti]> CREATE TRIGGER before_salary_update
    -> BEFORE UPDATE ON Employee
    -> FOR EACH ROW
    -> BEGIN
    ->     DECLARE salary_change DECIMAL(10, 2);
    ->
    ->     -- Calculate the percentage change in salary
    ->     SET salary_change = (NEW.salary - OLD.salary) / OLD.salary * 100;
    ->
    ->     -- Check if the change is within the 10% threshold
    ->     IF salary_change > 10 OR salary_change < -10 THEN
    ->         SIGNAL SQLSTATE '45000'
    ->         SET MESSAGE_TEXT = 'Error: Salary change exceeds 10% limit.';
    ->     END IF;
    -> END//
Query OK, 0 rows affected (0.182 sec)

MariaDB [crm_gesti]> DELIMITER ;
    • 1

relate perguntas