1

I am getting a MariaDB server version for the right syntax to use near 'END IF; END'

I've tried to remove the semi colons sitting getting the errors, I thought I had done it in the correct syntax.

Can anyone spot where I have messed up?

/* Trigger on account table to prevent users entering a opening balance less than 51 */
DELIMITER $$
CREATE TRIGGER account_opening_balance_check
BEFORE INSERT ON account
FOR EACH ROW 
BEGIN 
    IF NEW.opening_balance <= 50.00 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Your opening balance must be greater then 50.';
    ROLLBACK
    END IF;
END$$
DELIMITER ;
Rick James
  • 78,038
  • 5
  • 47
  • 113
paigelarry342
  • 29
  • 1
  • 5
  • Instead of a trigger for this, consider using a check constraint. E.g. ALTER TABLE account ADD CONSTRAINT ob_greater CHECK (opening_balance > 50); This will prevent inserts - as well as updates - from setting the opening_balance lower than 50 for any row. – dbdemon Dec 14 '21 at 16:10
  • @dbdemon ah thank you, what would you really use triggers for then? checks seem much better use case. Also is it possible to make a check to check that 2 bool type columns are not both true, or both false? – paigelarry342 Dec 14 '21 at 16:14
  • Yes, as long as the bools are in the same table. Triggers can do more complex logic, and can fire both before and after an insert or update. Checks were added to MariaDB in version 10.2.1, while triggers work on older versions as well. – dbdemon Dec 14 '21 at 16:37

1 Answers1

0

You forgot the semicolon for the ROLLBACK command:

/* Trigger on account table to prevent users entering a opening balance less than 51 */
DELIMITER $$
CREATE TRIGGER account_opening_balance_check BEFORE INSERT ON account
    FOR EACH ROW 
        BEGIN 
            IF NEW.opening_balance <= 50.00 THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Your opening balance must be greater then 50.';
                ROLLBACK;
            END IF;
        END$$
DELIMITER ;

UPDATE 2021-12-14 11:00

IF you are trying to get the trigger to stop doing the INSERT, you will need to deliberately put a logical runtime error in it.

I have a 10 year old post that shows you how to do this : Trigger in MySQL to prevent insertion

I got the idea from Chapter 11, Pages 254-256 of the book MySQL Stored Procedure Programming under the subheading 'Validating Data with Triggers'.

In your case, simply inject SQL that cannot run (such as selecting from a table that does not exist)

/* Trigger on account table to prevent users entering a opening balance less than 51 */
DELIMITER $$
CREATE TRIGGER account_opening_balance_check BEFORE INSERT ON account
    FOR EACH ROW 
        BEGIN 
            IF NEW.opening_balance <= 50.00 THEN
                SELECT num FROM mysql.dummytable WHERE id = 1;
            END IF;
        END$$
DELIMITER ;
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 1422 - Explicit or implicit commit is not allowed in stored function or trigger Iam getting this error now, I think its because of the ROLLBACK, is there any other way I can do this to stop the insertion of the opening_balance value if the if statement is met? – paigelarry342 Dec 14 '21 at 15:40
  • Thank you for the asnwer! however do I not need to handle the exception i throw with selecting from a random table that doesn't exist? – paigelarry342 Dec 14 '21 at 16:02
  • No, you don't. That is how you get out of a stored procedure when signal processing did not exist in the early days of MySQL Stored Procedures (which were invented in MySQL 5.0). The same take can still be used today. – RolandoMySQLDBA Dec 14 '21 at 16:15
  • I also have an old post about escaping check constraints : https://dba.stackexchange.com/questions/9662/check-constraint-does-not-work/9668#9668 – RolandoMySQLDBA Dec 14 '21 at 16:19
  • By now, check constraints and signal processing should be working normally. In the earlier years, they were not properly implemented in MySQL 5.x. – RolandoMySQLDBA Dec 14 '21 at 16:23