How to setup triggers in Mysql?

A trigger is a set of actions that are run automatically when a particularized change operation (SQL INSERT, UPDATE, or DELETE statement) is executed on a specified table. Triggers are beneficial for tasks such as implementing business rules, authorizing input data, and preserving an audit trail.

Types of Triggers

There are following different types of triggers in SQL, all of them can be encrypted for further security.  

  1. Insert
  2. Delete
  3. Update

Advantages of Triggers

  • Triggers help to improve the data integrity by minimizing the errors by checking the manipulation of the data according to business rules when certain actions performed on data.
  • Triggers are very useful to audit the changes of data in the table.

How to use Triggers

  • The syntax of the basic trigger is

CREATETRIGGER name ONtable
   [WITH ENCRYPTION]
   [FOR/AFTER/INSTEAD OF]
   [INSERT, UPDATE, DELETE]
   [NOTFORREPLICATION]

END  

Create Table

  • First, we will create the table for which the trigger is executed.
mysql> CREATE TABLE Cyblance Employees (age INT, name varchar(150));
  • Now, we will define the trigger. It will be executed before every Insert statement for the Cyblance employees table.

mysql> CREATE TRIGGER agecheck BEFORE INSERT ON Cyblance Employees FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END IF;//

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

  • Now we will insert the records to check the trigger functionality

mysql> INSERT INTO people VALUES (0, ‘vipul’), (27, ‘anil’),(22, ‘Nigam’);

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

  • At the end we will check the resultAt the end we will check the result.

mysql> SELECT * FROM people;

+——-+——-+

| age | Employee name |

+——-+——-+

| 0 | Vipul |

| 27 | Anil

|22 |Nigam

+——-+——-+

3 rows in set (0.00 sec)

Practice triggers to execute business rules, or to complete actions that have either a definite impact on the organization or if an action will hinder enigmas with the system.

Well, may be not before you go through this informative article that explains it right from the basics.Click here for more details.