Triggers in MySQL

What is a trigger

Simply put, a table has something (INSERT, delete, update operation), and then automatically trigger the execution of several pre-written SQL statements;

Features and functions

Features: The action that triggers the event and the SQL statement in the trigger is a transactional operation with Atomicity, either all executed, or none executed;

Role: To ensure the integrity of data, play a role in the constraints;

Example: Create a trigger, record a table's increment, delete, change operation record

Next, we will create the user and User_history table, as well as three triggers Tri_insert_user, Tri_update_user, Tri_delete_user, respectively, corresponding to the user table increment, delete, change three things;

• Create a user table;

DROP TABLE IF EXISTS`User`;CREATE TABLE`User' (' ID 'bigint( -) not NULLauto_increment, ' account 'varchar(255)DEFAULT NULL, ' name 'varchar(255)DEFAULT NULL, ' address 'varchar(255)DEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

• Create a History table for the user table operation;

DROP TABLE IF EXISTS' user_history ';CREATE TABLE' user_history ' (' ID ')bigint( -) not NULLauto_increment, 'user_id`bigint( -) not NULL, ' Operatetype 'varchar( $) not NULL, ' Operatetime 'datetime  not NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

• Create a user table insert event corresponding to the trigger Tri_insert_user;

A few notes:

DELIMITER: Change the input terminator, by default, the input terminator is a semicolon, and here it is changed to two semicolons;;, the purpose of this is to have a number of semicolon-containing statements in a package, all entered after the execution, rather than a default semicolon terminator is automatically executed;

new: Available when triggering insert and update events, pointing to the record being manipulated

old: available when triggering delete and update events, pointing to the record being manipulated

DROP TRIGGER IF EXISTS' Tri_insert_user ';D elimiter;;CREATE TRIGGER' Tri_insert_user ' afterINSERT  on`User` forEach ROWbegin    INSERT  intoUser_history (user_id, Operatetype, Operatetime)VALUES(New.id,'Add a user', now ());End;;D Elimiter;

• create a trigger for the User Table update event Tri_update_user ;

DROP TRIGGER IF EXISTS' Tri_update_user ';D elimiter;;CREATE TRIGGER' Tri_update_user ' afterUPDATE  on`User` forEach ROWbegin    INSERT  intoUser_history (user_id, Operatetype, Operatetime)VALUES(New.id,'Update a user', now ());End;;D Elimiter;

• Create a user table Delete event corresponding to the trigger Tri_delete_user;

DROP TRIGGER IF EXISTS' Tri_delete_user ';D elimiter;;CREATE TRIGGER' Tri_delete_user ' afterDELETE  on`User` forEach ROWbegin    INSERT  intoUser_history (user_id, Operatetype, Operatetime)VALUES(Old.id,'Delete a user', now ());End;;D Elimiter;

• At this point, all the tables and triggers are created, start validating the results, do insert, modify, delete the event, execute the following statement, to observe whether the user_history automatically generated operation record;

INSERT  into User(Account, name, address)VALUES('User1','User1','User1');INSERT  into User(Account, name, address)VALUES('User2','User2','User2');UPDATE User SETName= 'User3', account= 'User3', address='User3' whereName='User1';DELETE  from`User`whereName= 'User2';

• The results of the user table and the User_history table are observed, and the operation records have been generated, indicating that the trigger is working properly;

Triggers in MySQL