MySQL 8 Cookbook

How to do it...

For example, you want to round off the salary before inserting it into the salaries table. NEW refers to the new value that is being inserted:

shell> vi before_insert_trigger.sqlDROP TRIGGER IF EXISTS salary_round;DELIMITER $$CREATE TRIGGER salary_round BEFORE INSERT ON salariesFOR EACH ROWBEGIN           SET NEW.salary=ROUND(NEW.salary);END$$DELIMITER ;

Create the trigger by sourcing the file:

mysql> SOURCE before_insert_trigger.sql;Query OK, 0 rows affected (0.06 sec)Query OK, 0 rows affected (0.00 sec)

Test the trigger by inserting a floating number into the salary:

mysql> INSERT INTO salaries VALUES(10002, 100000.79, CURDATE(), '9999-01-01');Query OK, 1 row affected (0.04 sec)

You can see that the salary is rounded off:

mysql> ...