( SQL STOP INSERT SOME ROWS AS PER SOME CONDITIONS )

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.

Leave a Reply