CODEWITHSUNDEEP
sqlmysql
Igor
Igor

Reputation: 2783

MySQL Trigger to prevent INSERT under certain conditions

I want to make a trigger that will prevent the insertion if the birthdate (one of the columns) is in the future. I have this:

CREATE TRIGGER foo
BEFORE INSERT ON table
FOR EACH ROW
BEGIN
  IF NEW.birthdate > CURRENT_DATE()
  THEN
    //How do I prevent the insert right here???
  END IF;
END;

How can I cancel the insert inside the if statement?

Upvotes: 57

Views: 97186

Answers (6)

mugume Johnson
mugume Johnson

Reputation: 141

DELIMITER $$
CREATE TRIGGER foo
BEFORE INSERT ON table
FOR EACH ROW
BEGIN
  IF NEW.birthdate > CURRENT_DATE()
  THEN
   SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Warning: birthdate can not be greater than current date!';
  END IF;

END$$
    DELIMITER ;

Upvotes: 14

Black Butterfly
Black Butterfly

Reputation: 359

I don't know if it's to late but you can do that now : 

SIGNAL SQLSTATE '45000' 
SET MESSAGE_TEXT = "your error text";

P.S. Don't forget to modify the delimiter before and after the trigger...

Upvotes: 23

MJBZA
MJBZA

Reputation: 5018

I had the same problem. I created a table but there was no way to assign the minimum length of string in MySQL. I created the table as follows:

CREATE TABLE Users
           (
             UserName varchar(15) NOT NULL PRIMARY KEY,
             Password varchar(15) NOT NULL,
             Active Bool DEFAULT TRUE,
             CHECK (CHAR_LENGTH(UserName)>3)
           )

But the CHECK function doesn't work on insert.

So I changed my Table as follow :

CREATE TABLE Users
           (
             UserName varchar(15) NOT NULL PRIMARY KEY,
             Password varchar(15) NOT NULL,
             Active Bool DEFAULT TRUE
           )

After that, create a Trigger:

delimiter $$
CREATE TRIGGER myTrigger
   BEFORE INSERT ON Users
   FOR EACH ROW
 BEGIN
   IF CHAR_LENGTH(NEW.UserName) < 4 THEN
     DELETE FROM Users WHERE Users.UserName=NEW.UserName;
   END if;
 END$$
delimiter ;

Upvotes: -1

Eric Leschinski
Eric Leschinski

Reputation: 153872

Example 1, MySQL, You can cancel that insert in the trigger with signal sqlstate

  1. Create your table with a varchar column:

    mysql> create table yar (val VARCHAR(25) not null);
Query OK, 0 rows affected (0.02 sec)

  2. Create your 'before insert' trigger to check for a condition and disallow.

    mysql> delimiter $$
mysql> create trigger foo before insert on yar
    -> for each row
    -> begin
    -> if new.val = '' then
    -> signal sqlstate '45000';
    -> end if;
    -> end;$$
Query OK, 0 rows affected (0.01 sec)

  3. Try to insert where the condition is met:

    mysql> delimiter ;

mysql> insert into yar values("");
ERROR 1644 (45000): Unhandled user-defined exception condition

mysql> insert into yar values ("abc");
Query OK, 1 row affected (0.01 sec)

mysql> select * from yar;
+-----+
| val |
+-----+
| abc |
+-----+
1 row in set (0.00 sec)

You inserted a blank string, the trigger saw it was blank and raised the signal to prevent the insert.

Example 2, MySQL, Cancel the insert in the trigger by causing the data to violate a not null constraint.

  1. Create your table with a varchar column:

    mysql> create table yar (val VARCHAR(25) not null);
Query OK, 0 rows affected (0.02 sec)

  2. Create your 'before insert' trigger to check for a condition and disallow.

    mysql> delimiter $$
mysql> create trigger foo before insert on yar
    -> for each row
    -> begin
    -> if new.val = '' then
    -> set new.val = NULL;
    -> end if;
    -> end;$$
Query OK, 0 rows affected (0.01 sec)

  3. Try to insert where the condition is met:

    mysql> delimiter ;

mysql> insert into yar values("");
ERROR 1048 (23000): Column 'val' cannot be null

mysql> insert into yar values ("abc");
Query OK, 1 row affected (0.01 sec)

mysql> select * from yar;
+-----+
| val |
+-----+
| abc |
+-----+
1 row in set (0.00 sec)

You inserted a blank string, the trigger saw it was blank and changed the value to null, so the insert is prevented.

Upvotes: 70

MaHuJa
MaHuJa

Reputation: 3188

The problem described in the question sounds like a perfect candidate for a CHECK constraint - add this line to the table definition.

CONSTRAINT born_in_the_past CHECK (birthdate<=now())

Upvotes: -2

Rich Adams
Rich Adams

Reputation: 26574

Based on this I'm not sure if it's possible to do it that way.

There's no support in MySQL's current implementation of triggers to voluntarily throw an exception and abort the statement that spawned the trigger.

The workaround I've found is to write a BEFORE trigger to set one of the not-NULL columns in the table to NULL, thus violating its NOT NULL constraint. This causes the statement that spawned the trigger to be aborted.

Upvotes: 7

Related Questions