O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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> ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required