Basic learning tutorial for triggers in MySQL _ MySQL

This article mainly introduces the basic learning tutorial of MySQL triggers, including the basic knowledge of trigger creation and management! For more information, see 0. basic concepts of triggers
A trigger is a special stored procedure that triggers execution when inserting, deleting, or modifying data in a specific table, it has more refined and complex data control capabilities than the standard functions of the database itself.

Database triggers have the following functions:

(1). security. Users can have certain rights to operate databases based on database values.

# User operations can be restricted based on time, for example, database data cannot be modified after work or holidays.

# User operations can be restricted based on the data in the database. for example, the stock price cannot be increased by more than 10% at a time.

(2) audit. You can track your database operations.

# Audit user statements for database operations.

# Write updates to the database into the audit table.

(3). implement complex data integrity rules

# Implement non-standard data integrity check and constraints. Triggers can generate more complex limits than rules. Unlike rules, triggers can reference columns or database objects. For example, a trigger can be used to roll back any attempt to eat futures that exceed its deposit.

# Provides variable default values.

(4). implement complex non-standard database integrity rules. Triggers can be used to update related tables in the database. For example, the deletion trigger on the author_code column of the auths table can cause the row to be deleted from other tables.

# Cascade the modification or deletion of matched rows in other tables.

# When modifying or deleting a table, set the matched rows in other tables to NULL values.

# When modifying or deleting a table, set the row cascade matching the table to the default value.

# The trigger can reject or roll back the changes that undermine the integrity of the relevant, and cancel the transactions that attempt to update the data. This trigger works when an external key that does not match its primary key is inserted. For example, an insert trigger can be generated in the books. author_code column. if the new value does not match a value in the auths. author_code column, the insert is rolled back.

(5). synchronize the data in the table in real time.

(6) automatically calculate the data value. if the value meets certain requirements, specific processing will be performed. For example, if the company account has funds less than 50 thousand yuan, the company immediately sends a warning data to the finance staff.

1. create trigger Syntax

CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_bodytrigger_time: { BEFORE | AFTER }trigger_event: { INSERT | UPDATE | DELETE }CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body trigger_time: { BEFORE | AFTER }trigger_event: { INSERT | UPDATE | DELETE }

Syntax description:
1.1 Authorization and revocation
To CREATE a TRIGGER, you must have the create trigger permission:

grant create trigger on `database_naem`.`table_name` to `user_name`@`ip_address`;grant create trigger on `database_naem`.`table_name` to `user_name`@`ip_address`;

Revoke permissions:

revoke create trigger on `database_naem`.`table_name` from `user_name`@`ip_address`;revoke create trigger on `database_naem`.`table_name` from `user_name`@`ip_address`;

1.2 trigger_name
The trigger command must be provided. it can contain a maximum of 64 characters. we recommend that you use the abbreviated table name_trigger type. For example, ttlsa_posts_bi (table ttlsa_posts, where the trigger occurs before insert)
1.3 DEFINER clause
When activating a trigger, check the access permission to ensure the safe use of the trigger.
1.4 trigger_time
Defines the trigger time. It can be set to occur before or after a row record change.
1.5 trigger_event
Define trigger events. The following events are triggered:
1.5.1
INSERT: triggered when a new row is inserted into the table. Such as INSERT, load data, and REPLACE statements.
UPDATE: triggered when a row of data is changed. Such as the UPDATE statement.
DELETE: triggered when a row is deleted from the table. Such as DELETE and REPLACE statements. Note: The drop table and truncate table statements do not trigger this trigger because they do not use DELETE. Deleting a partition table is not triggered.
There is a potential confusion, such as insert into... on duplicate key update... depends ON whether duplicate key rows exist.
You cannot create multiple triggers with the same trigger event and trigger time for a table. If you cannot create two before update triggers for a table, you can create one before update, one before insert, one before update, and one after update trigger.
1.6 for each row clause
Defines the trigger execution interval. The for each row clause defines that the trigger executes an action every ROW instead of the entire table.
1.7 trigger_body clause
Contains the SQL statement to be triggered. It can be any legal statement, including a composite statement (you need to use BEGIN... END structure), flow control statements (if, case, while, loop, for, repeat, leave, iterate), variable declaration (declare) and assignment (set), exception handling declaration, conditional declaration is allowed, but the statements here are subject to the same restrictions as those of functions.
1.7.1 OLD and NEW
In the SQL statement of the trigger, any column in the table can be associated and identified by using the OLD and NEW column names, such as OLD. col_name and NEW. col_name. OLD. col_name associates a column of an existing row with the value before it is updated or deleted. NEW. col_name associates with a NEW row to insert or update the values of a column in an existing row.
Only NEW statements are valid for INSERT statements. Otherwise, an ERROR occurs: ERROR 1363 (HY000): There is no OLD row in on INSERT trigger.
Only OLD statements are valid for DELETE statements. Otherwise, an ERROR occurs: ERROR 1363 (HY000): There is no NEW row in on DELETE trigger.
For the UPDATE statement, both NEW and OLD can be used.
2. instance
2.1 create a table
Use the table created in mysqludf_json to encode relational data in JSON format. User tables will be migrated to nosql databases in the future.

mysql> create table `ttlsa_users` (  -> `uid` int(11) unsigned,  -> `username` varchar(40) NOT NULL,  -> `password` varchar(40) NOT NULL,  -> `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -> PRIMARY KEY (`uid`)  -> );mysql> create table `ttlsa_users` (  -> `uid` int(11) unsigned,  -> `username` varchar(40) NOT NULL,  -> `password` varchar(40) NOT NULL,  -> `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -> PRIMARY KEY (`uid`)  -> );

Create another table to store trigger action data.

mysql> create table `ttlsa_users3` (  -> `uid` int(11) unsigned,  -> `userinfo` varchar(200),  -> );mysql> create table `ttlsa_users3` (  -> `uid` int(11) unsigned,  -> `userinfo` varchar(200),  -> );

2.2 Create a trigger

mysql> delimiter //mysql> create trigger ttlsa_users_ai  -> after insert on ttlsa_users  -> for each row  -> insert into ttlsa_users3 (uid, userinfo) values(uid, json_object(NEW.uid, NEW.username, NEW.password));  -> //mysql> create trigger ttlsa_users_au  -> after update on ttlsa_users  -> for each row  -> update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid;  -> //mysql> delimiter //mysql> create trigger ttlsa_users_ai  -> after insert on ttlsa_users  -> for each row  -> insert into ttlsa_users3 (uid, userinfo) values(uid, json_object(NEW.uid, NEW.username, NEW.password));  -> // mysql> create trigger ttlsa_users_au  -> after update on ttlsa_users  -> for each row  -> update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid;  -> //

2.3 Test

mysql> insert into ttlsa_users values (890,'xuhh',md5('abc'),NULL,'test trigger')//

Query OK, 1 row affected (0.01 sec)

mysql> select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+| uid | username  | password             | createtime     | json_data             |+-----+-------------+----------------------------------+---------------------+------------------------------------+| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" || 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh                || 890 | xuhh    | 900150983cd24fb0d6963f7d28e17f72 | 2013-08-14 16:40:49 | test trigger            |+-----+-------------+----------------------------------+---------------------+------------------------------------+3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+| userinfo                                  | uid |+-----------------------------------------------------------------------------+------+| {"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"} | 890 |+-----------------------------------------------------------------------------+------+2 rows in set (0.00 sec)

mysql> update ttlsa_users set password='test_update' where uid=890//

Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+| uid | username  | password             | createtime     | json_data             |+-----+-------------+----------------------------------+---------------------+------------------------------------+| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" || 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh                || 890 | xuhh    | test_update           | 2013-08-14 16:41:33 | test trigger            |+-----+-------------+----------------------------------+---------------------+------------------------------------+3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+| userinfo                                  | uid |+-----------------------------------------------------------------------------+------+| {"uid":890,"username":"xuhh","password":"test_update"}           | 890 |+-----------------------------------------------------------------------------+------+2 rows in set (0.00 sec)

mysql> insert into ttlsa_users values (890,'xuhh',md5('abc'),NULL,'test trigger')//

Query OK, 1 row affected (0.01 sec)

mysql> select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+| uid | username  | password             | createtime     | json_data             |+-----+-------------+----------------------------------+---------------------+------------------------------------+| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" || 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh                || 890 | xuhh    | 900150983cd24fb0d6963f7d28e17f72 | 2013-08-14 16:40:49 | test trigger            |+-----+-------------+----------------------------------+---------------------+------------------------------------+3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+| userinfo                                  | uid |+-----------------------------------------------------------------------------+------+| {"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"} | 890 |+-----------------------------------------------------------------------------+------+2 rows in set (0.00 sec) 

mysql> update ttlsa_users set password='test_update' where uid=890//

Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+| uid | username  | password             | createtime     | json_data             |+-----+-------------+----------------------------------+---------------------+------------------------------------+| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" || 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh                || 890 | xuhh    | test_update           | 2013-08-14 16:41:33 | test trigger            |+-----+-------------+----------------------------------+---------------------+------------------------------------+3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+| userinfo                                  | uid |+-----------------------------------------------------------------------------+------+| {"uid":890,"username":"xuhh","password":"test_update"}           | 890 |+-----------------------------------------------------------------------------+------+2 rows in set (0.00 sec)

3. Management
3.1 List triggers

Mysql> show triggers like '% ttlsa %'; Trigger name matching % ttlsa %

*************************** 1. row ***************************       Trigger: ttlsa_users_ai        Event: INSERT        Table: ttlsa_users      Statement: insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password))       Timing: AFTER       Created: NULL      sql_mode: NO_ENGINE_SUBSTITUTION       Definer: root@127.0.0.1character_set_client: utf8collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci*************************** 2. row ***************************       Trigger: ttlsa_users_au        Event: UPDATE        Table: ttlsa_users      Statement: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid       Timing: AFTER       Created: NULL      sql_mode: NO_ENGINE_SUBSTITUTION       Definer: root@127.0.0.1character_set_client: utf8collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci2 rows in set (0.00 sec)

Mysql> show triggers; # List all mysql> show triggers from database_name; # List database TRIGGERS mysql> show create trigger trigger_name; # View CREATE TRIGGER

*************************** 1. row ***************************        Trigger: ttlsa_users_ai       sql_mode: NO_ENGINE_SUBSTITUTIONSQL Original Statement: CREATE DEFINER=`root`@`127.0.0.1` trigger ttlsa_users_ai after insert on ttlsa_users for each row insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password)) character_set_client: utf8 collation_connection: utf8_general_ci  Database Collation: latin1_swedish_ci1 row in set (0.01 sec)

3.2 INFORMATION_SCHEMA.TRIGGERS table

SQL> SHOW TRIGGERS like '% ttlsa %'; # trigger name matching % ttlsa %

*************************** 1. row ***************************       Trigger: ttlsa_users_ai        Event: INSERT        Table: ttlsa_users      Statement: insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password))       Timing: AFTER       Created: NULL      sql_mode: NO_ENGINE_SUBSTITUTION       Definer: root@127.0.0.1character_set_client: utf8collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci*************************** 2. row ***************************       Trigger: ttlsa_users_au        Event: UPDATE        Table: ttlsa_users      Statement: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid       Timing: AFTER       Created: NULL      sql_mode: NO_ENGINE_SUBSTITUTION       Definer: root@127.0.0.1character_set_client: utf8collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci2 rows in set (0.00 sec)

Mysql> show triggers; # List all mysql> show triggers from database_name; # List database TRIGGERS mysql> show create trigger trigger_name; # View CREATE TRIGGER

*************************** 1. row ***************************        Trigger: ttlsa_users_ai       sql_mode: NO_ENGINE_SUBSTITUTIONSQL Original Statement: CREATE DEFINER=`root`@`127.0.0.1` trigger ttlsa_users_ai after insert on ttlsa_users for each row insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password)) character_set_client: utf8 collation_connection: utf8_general_ci  Database Collation: latin1_swedish_ci1 row in set (0.01 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ttlsa_users_au'\G

*************************** 1. row ***************************      TRIGGER_CATALOG: def      TRIGGER_SCHEMA: test       TRIGGER_NAME: ttlsa_users_au    EVENT_MANIPULATION: UPDATE   EVENT_OBJECT_CATALOG: def    EVENT_OBJECT_SCHEMA: test    EVENT_OBJECT_TABLE: ttlsa_users       ACTION_ORDER: 0     ACTION_CONDITION: NULL     ACTION_STATEMENT: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid    ACTION_ORIENTATION: ROW       ACTION_TIMING: AFTERACTION_REFERENCE_OLD_TABLE: NULLACTION_REFERENCE_NEW_TABLE: NULL ACTION_REFERENCE_OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW          CREATED: NULL         SQL_MODE: NO_ENGINE_SUBSTITUTION          DEFINER: root@127.0.0.1   CHARACTER_SET_CLIENT: utf8   COLLATION_CONNECTION: utf8_general_ci    DATABASE_COLLATION: latin1_swedish_ci1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ttlsa_users_au'\G

*************************** 1. row ***************************      TRIGGER_CATALOG: def      TRIGGER_SCHEMA: test       TRIGGER_NAME: ttlsa_users_au    EVENT_MANIPULATION: UPDATE   EVENT_OBJECT_CATALOG: def    EVENT_OBJECT_SCHEMA: test    EVENT_OBJECT_TABLE: ttlsa_users       ACTION_ORDER: 0     ACTION_CONDITION: NULL     ACTION_STATEMENT: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid    ACTION_ORIENTATION: ROW       ACTION_TIMING: AFTERACTION_REFERENCE_OLD_TABLE: NULLACTION_REFERENCE_NEW_TABLE: NULL ACTION_REFERENCE_OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW          CREATED: NULL         SQL_MODE: NO_ENGINE_SUBSTITUTION          DEFINER: root@127.0.0.1   CHARACTER_SET_CLIENT: utf8   COLLATION_CONNECTION: utf8_general_ci    DATABASE_COLLATION: latin1_swedish_ci1 row in set (0.00 sec)

3.3 delete a trigger

mysql> drop trigger trigger_name;mysql> drop trigger trigger_name;