MySQL Trigger to prevent INSERT under certain conditions

by

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.

More Related Contents:

  1. MySQL: Alternatives to ORDER BY RAND()
  2. Simple way to calculate median with MySQL
  3. MySQL LIKE IN()?
  4. Auto Increment after delete in MySQL
  5. Unioning two tables with different number of columns
  6. In MySQL queries, why use join instead of where?
  7. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  8. MySQL “NOT IN” query
  9. Error 1046 No database Selected, how to resolve?
  10. Emulate MySQL LIMIT clause in Microsoft SQL Server 2000
  11. Importance of varchar length in MySQL table
  12. Is there something wrong with joins that don’t use the JOIN keyword in SQL or MySQL?
  13. mySQL :: insert into table, data from another table?
  14. Return 0 if field is null in MySQL
  15. MySQL “between” clause not inclusive?
  16. MySQL syntax for Join Update
  17. SQL – How to transpose?
  18. SQL SELECT to get the first N positive integers
  19. Sorting string column containing numbers in SQL?
  20. Multiple Ranks in one table
  21. The difference between ‘AND’ and ‘&&’ in SQL
  22. Can I use a MySQL database with an App Engine application
  23. How can I make an average of dates in MySQL?
  24. What is the best way to generate ranks in MYSQL?
  25. What does collation mean?
  26. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  27. sql pulling a row for next or previous row of a current row
  28. Row Rank in a MySQL View
  29. Select Rows with Maximum Column Value group by Another Column
  30. mysql GROUP_CONCAT duplicates

Leave a Comment