MySQL Restrict Insert

Question

How do I do the equivalent statement in MySQL? I want it if input is a certain value, don't insert the row. In SQL SERVER, I can just say ROLLBACK. What's the equivalent command in MySQL? Thank you.

CREATE TRIGGER tr_some_trigger
ON some_table
FOR INSERT
AS 
BEGIN
  IF inserted.topic == 'test' THEN
    ROLLBACK
  ENDIF
END

james_bond · Accepted Answer · 2015-04-13T04:10:06.003

2

From this document:

The trigger cannot use statements that explicitly or implicitly begin or end a transaction such as START TRANSACTION, COMMIT, or ROLLBACK.

So ROLLBACK won't work inside your trigger, but if the trigger raises an exception/error, it will prevent the insertion to succeed, so what you can do is raise an exception if your condition is met (one way is to call an undefined function).

For example:

DELIMITER $$

CREATE
    TRIGGER `db`.`before_insert` BEFORE INSERT
    ON `db`.`dummy_table`
    FOR EACH ROW BEGIN
    IF new.topic = 'test' THEN
        CALL func_1();
    END IF;

    END$$

DELIMITER ;

Assuming func_1 doesn't exist it will prevent your new record of being inserted.

edited Apr 13 '15 at 04:10

answered Jun 21 '12 at 23:50

james_bond

6,778
3
28
34

Is this the only way? Is it a good practice to just call a function that doesn't exist to make it fail? If for some unfortunate reason, someone created a function with the same name, the trigger won't work anymore. – Eric Jun 25 '12 at 18:42
There is no throw or raise keyword in mysql, so that's one way, for the other ones look at this thread http://stackoverflow.com/questions/465727/raise-error-within-mysql-function – james_bond Jun 25 '12 at 19:03

score 1 · Answer 2 · answered Jun 21 '12 at 23:36

1

It's rollback in MySQL too. Check out the documentation here.

answered Jun 21 '12 at 23:36

Abe Miessler

82,532
99
305
486

MySQL Restrict Insert

2 Answers2