The two triggers to ensure valid data. : Before Trigger « Trigger « SQL / MySQL

1. SQL / MySQL
2. Trigger
3. Before Trigger

The two triggers to ensure valid data.

       

mysql>
mysql> CREATE TABLE test (id SERIAL, percent DOUBLE);
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER $$
mysql>
mysql> CREATE TRIGGER test_before_insert
    ->    BEFORE INSERT ON test FOR EACH ROW
    ->    BEGIN
    ->       IF NEW.percent < 0.0 OR NEW.percent > 1.0 THEN
    ->           SET NEW.percent = NULL;
    ->       END IF;
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TRIGGER test_before_update
    -> BEFORE UPDATE ON test FOR EACH ROW
    -> BEGIN
    ->    IF NEW.percent < 0.0 OR NEW.percent > 1.0 THEN
    ->       SET NEW.percent = NULL;
    ->    END IF;
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql>
mysql> INSERT INTO test (percent) VALUES (-1), (0.3), (1.5);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM test;
+----+---------+
| id | percent |
+----+---------+
|  1 |    NULL |
|  2 |     0.3 |
|  3 |    NULL |
+----+---------+
3 rows in set (0.00 sec)

mysql>
mysql> UPDATE test SET percent = 1.7 WHERE id =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> SELECT * FROM test;
+----+---------+
| id | percent |
+----+---------+
|  1 |    NULL |
|  2 |    NULL |
|  3 |    NULL |
+----+---------+
3 rows in set (0.00 sec)

mysql>
mysql>
mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql>

   
    
    
    
    
    
    
  

Related examples in the same category