CODEWITHSUNDEEP
mysql
Eric
Eric

Reputation: 3257

MySQL Restrict Insert

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

Upvotes: 1

Views: 3016

Answers (2)

james_bond
james_bond

Reputation: 6908

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.

Upvotes: 2

Abe Miessler
Abe Miessler

Reputation: 85046

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

Upvotes: 1

Related Questions