mysql Trigger Creation Using a Simple Example of Triggers

This article example describes the creation of mysql triggers using triggers. Share it for your reference, as follows:

We can use the CREATE TRIGGER statement to create a new trigger and look at the specific syntax:

CREATE TRIGGER trigger_name trigger_time trigger_event
 ON table_name
 FOR EACH ROW
 BEGIN
 ...
 END;

Then let's take a closer look at the specific meaning of the above sql:

Put the trigger name after the CREATE TRIGGER statement. Trigger names should follow the naming convention [trigger time] _ [table name] _ [trigger event], such as before_employees_update. The trigger activation time can be before or after. You must specify an activation time for the defined trigger. Use the BEFORE keyword if you want to process the action before the change, and the AFTER keyword if you need to process the action after the change. The trigger event can be INSERT, UPDATE, or DELETE. This event causes the trigger to be called. Triggers can only be called by 1 event. To define triggers that are invoked by multiple events, you must define multiple triggers, one for each event. Triggers must be associated with a specific table. No table triggers will not exist, so you must specify a table name after the ON keyword. Place the SQL statement between the BEGIN and END blocks. This is where the trigger logic is defined.

After getting a general understanding, let's try to create triggers to record the changes of row data in employees table. First, let's look at the structure of this table:

mysql> DESC employees;
+----------------+--------------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11)   | NO  | PRI | NULL  |    |
| lastName    | varchar(50) | NO  |   | NULL  |    |
| firstName   | varchar(50) | NO  |   | NULL  |    |
| extension   | varchar(10) | NO  |   | NULL  |    |
| email     | varchar(100) | NO  |   | NULL  |    |
| officeCode   | varchar(10) | NO  | MUL | NULL  |    |
| reportsTo   | int(11)   | YES | MUL | NULL  |    |
| jobTitle    | varchar(50) | NO  |   | NULL  |    |
+----------------+--------------+------+-----+---------+-------+
8 rows in set

Let's create a new table named employees audit to hold the changes to the data in the employees table:

CREATE TABLE employees_audit (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employeeNumber INT NOT NULL,
  lastname VARCHAR(50) NOT NULL,
  changedat DATETIME DEFAULT NULL,
  action VARCHAR(50) DEFAULT NULL
);

Then create an BEFORE UPDATE trigger, which is called before the row record in the employees table is changed:

DELIMITER $$
CREATE TRIGGER before_employee_update 
  BEFORE UPDATE ON employees
  FOR EACH ROW 
BEGIN
  INSERT INTO employees_audit
  SET action = 'update',
   employeeNumber = OLD.employeeNumber,
    lastname = OLD.lastname,
    changedat = NOW(); 
END$$
DELIMITER ;

In the body of the trigger above, we use the OLD keyword to access the employeeNumber and lastname columns of the row affected by the trigger. We should note that in the triggers defined for insert, only the NEW keyword can be used. The OLD keyword cannot be used. However, there are no new rows in the triggers defined for DELETE, so you can only use the OLD keyword. In an update trigger, OLD is the row before updating, and NEW is the row after updating.

We can then use the SHOW TRIGGERS statement to view the triggers in the database:

mysql> SHOW TRIGGERS;
+------------------------+--------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger        | Event | Table   | Statement                                                                               | Timing | Created        | sql_mode                                     | Definer    | character_set_client | collation_connection | Database Collation |
+------------------------+--------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| before_employee_update | UPDATE | employees | BEGIN
  INSERT INTO employees_audit
  SET action = 'update',
   employeeNumber = OLD.employeeNumber,
    lastname = OLD.lastname,
    changedat = NOW();
END | BEFORE | 2017-08-02 22:06:36.40 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8         | utf8_general_ci   | utf8_general_ci  |
+------------------------+--------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set

When we are done, we update the employees table to check whether the trigger is called:

UPDATE employees 
SET 
  lastName = 'Maxsu'
WHERE
  employeeNumber = 1056;

You can use the following query to query the employees_audit table to check whether a trigger is invoked by an UPDATE statement:

mysql> SELECT * FROM employees_audit;
+----+----------------+----------+---------------------+--------+
| id | employeeNumber | lastname | changedat      | action |
+----+----------------+----------+---------------------+--------+
| 1 |      1056 | Hill   | 2017-08-02 22:15:51 | update |
+----+----------------+----------+---------------------+--------+
1 row in set

As shown in the output above, the trigger is actually invoked and a new row is inserted into the employees_audit table.

Ok, that's all for this record.

More readers interested in MySQL can see the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Related Skills Summary" and "MySQL Common Functions Summary"

I hope this article is helpful to everyone's MySQL database.