What are MySQL triggers? And steps to use them

kumkumsharma

Administrator
Staff member
MySQL triggers is an object which is attached with particular table and execute when that event occur. These triggers only work when any of these Mysql statements run like INSERT, UPDATE, DELETE etc.

NOTE: You can only use triggers if you have MySQL SUPERUSER privileges. So you have your own VPS or Dedicated server. You cannot get SUPERUSER privileges on shared server.

You can check below example for more details:
  • First login to SSH on your server.
  • And then create table with below command:
Code:
mysql> CREATE TABLE people (age INT, name varchar(150));
  • Now we will define trigger and it will be execute before every INSERT statement.
Code:
mysql> delimiter //mysql> CREATE TRIGGER agecheck BEFORE INSERT ON people FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END IF;// Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
  • we can check our trigger functionality by inserting 2 records:
Code:
mysql> INSERT INTO people VALUES (-20, ‘Ahana), (30, ‘Marcus);Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0
  • At last check your table:
Code:
mysql> SELECT * FROM people;+——-+——-+| age | name |+——-+——-+| 0 | Ahana || 30 | Mark |+
 
Top