I'm trying to create a trigger using phpmyadmin with bank MariaDB but when it arrives in the if line it gives a message:
unrecognized statement type. (near IF)
The trigger is this:
SET @TOTAL = (SELECT COUNT(*) FROM cliente C WHERE C.NR_CPF = NEW.NR_CPF);
IF @TOTAL > 0 THEN BEGIN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'An error occurred'
END IF;
I believe the correct one, maintaining this logic, would be:
BEGIN
DECLARE total BIGINT;
SELECT COUNT(*)
INTO @total
FROM cliente C
WHERE C.NR_CPF = NEW.NR_CPF;
IF @total > 0 THEN
SIGNAL SQLSTATE '45000' SET message_text = 'An error occurred';
END IF;
END
Another way would be to just add BEGIN and END and a ; to correct, like this:
BEGIN
SET @total = (SELECT COUNT(*) FROM cliente C WHERE C.NR_CPF = NEW.NR_CPF);
IF @total > 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'An error occurred';
END IF;
END