MySQL error code #1064 near IF ELSE block when creating a Trigger Before Insert

Question

I am trying to put a constraint on a database where if generic_asset.type = 'raw' then generic_asset.atomic = 1 must be maintained. For this I wrote the following trigger of type BEFORE INSERT. Here is the snippet:

DELIMITER //

CREATE TRIGGER generic_asset_check BEFORE INSERT ON generic_asset FOR EACH ROW
BEGIN
   IF NEW.type = 'raw' THEN
   BEGIN
       IF NEW.atomic = 0 THEN
           SET SQLSTATE = 'Sorry cannot insert';
       END IF;
   END IF;
END //


DELIMITER ;

Error is like:

#1064 - syntax error near 'SQLSTATE = 'Sorry cannot insert';
       END IF;
   END IF;
END' in line 6

(translated from French).

I tried various syntax but all seam not to work and also knowing that my changes are so little like changing double quotes, removing BEGIN with END IF;... So I know these turns are irreverent.

I revised syntax in many internet resources and official documentation, nothing helped.

GMB · Accepted Answer · 2020-09-10 21:13:49Z

1

MySQL's IF statement does not take a BEGIN keyword. Also, if you want to raise an error from within the trigger, you need SIGNAL. Finally, these two nested conditions can be flattened.

Consider:

DELIMITER //
CREATE TRIGGER generic_asset_check BEFORE INSERT ON generic_asset FOR EACH ROW
BEGIN
   IF NEW.type = 'raw' AND NEW.atomic = 0 THEN
       SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sorry cannot insert';
   END IF;
END //
DELIMITER ;

Demo on DB Fiddle

answered Sep 10, 2020 at 21:13

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL error code #1064 near IF ELSE block when creating a Trigger Before Insert

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related