Basic Learning tutorials for triggers in Mysql _mysql

0. Basic concepts of Triggers
a trigger is a special stored procedure that triggers execution when inserting, deleting, or modifying data in a particular table, and it has a finer and more complex data control capability than the standard functionality of the database itself.

Database triggers have the following effects:

(1). Safety. You can make a user have some right to manipulate the database based on the value of the database.

# You can limit the user's actions based on time, such as not allowing you to modify database data after work and holidays.

# You can restrict the user's actions based on data in the database, such as not allowing the price of a stock to increase by more than 10%.

(2). Audit. You can track user actions on a database.

# The statement that audits the user to manipulate the database.

# Write user updates to the database to the audit table.

(3). Implementing Complex data integrity rules

# Implementation of non-standard data integrity checks and constraints. Triggers can produce more complex restrictions than rules. Unlike rules, triggers can refer to columns or database objects. For example, triggers can roll back any futures that attempt to eat more than their margin.

# provides a variable default value.

(4). Implementation of complex non-standard database-related integrity rules. Triggers can have a serial update of related tables in the database. For example, a delete trigger on a auths table Author_code column can cause a corresponding deletion of the rows that match in the other table.

# cascade Modify or delete rows that match in other tables when modified or deleted.

# Set the matching rows in other tables to null values when you modify or delete them.

# Set the matching rows in other tables to their default values when you modify or delete them.

# triggers can reject or rewind changes that break the associated integrity and cancel the transaction that is attempting to update the data. This trigger works when you insert a foreign key that does not match its primary health. For example, you can generate an INSERT trigger on the Books.author_code column, and the insertion is rolled back if the new value does not match a value in the Auths.author_code column.

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

(6). Automatic calculation of data values, if the value of the data to meet certain requirements, then the specific processing. For example, if the company's account number is less than 50,000 yuan, immediately send the warning data to the financial staff.

1. Create TRIGGER Syntax

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}

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 related section Description:
1.1 Authorization and recycling
CREATE TRIGGER permissions are required for creating triggers:

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 ';

Permission retract:

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 given up to 64 characters, and the name of the table-the abbreviation for the trigger type-is recommended. such as TTLSA_POSTS_BI (table ttlsa_posts, triggers occur before the insert before)
1.3 definer clause
When activating a trigger, check access permissions to ensure that the trigger is safe to use.
1.4 Trigger_time
Defines the trigger time for triggers. Can be set to occur before or after the row record changes.
1.5 trigger_event
Defines trigger trigger events. The events that are triggered are:
1.5.1
Insert: Triggers when a new row is inserted into the table. such as INSERT, LOAD data, and replace statements.
UPDATE: Triggers when a row of data is changed. such as the UPDATE statement.
Delete: Triggers when a row is deleted from the table. such as the delete and replace statements. Note: The DROP table and TRUNCATE TABLE statements do not trigger the trigger because they are not using delete. Also deleting a partition table does not trigger.
There is a potential for confusion, such as insert INTO ... On DUPLICATE KEY UPDATE ... Depends on whether there are duplicate key rows.
You cannot create multiple triggers with the same trigger event and trigger time on a table. If you cannot create two before update triggers for a table, you can create a before update and a before insert or a before update and an after update trigger.
1.6 for each row clause
Defines the trigger execution interval. The For each row clause defines a trigger to perform an action every other row instead of executing the entire table one time.
1.7 trigger_body clause
Contains the SQL statement to trigger execution. Can be any legitimate statement, including compound statements (you need to use the begin ...) End structure), flow control statements (if, case, while, loop, for, repeat, leave, iterate), variable declarations (declare), and assignment (set), exception handling declarations, allow condition declarations, But the statements here are subject to the same restrictions as the functions.
1.7.1 Old and new
In the SQL statement of a trigger, you can associate any column in the table by using the old and NEW column names to identify them, such as Old.col_name, New.col_name. Old.col_name the value of a column of existing rows before being updated or deleted. New.col_name the value of a column that inserts or updates an existing row that is associated with a new row.
For INSERT statements, only new is legal. Otherwise there will be an error: Error 1363 (HY000): There is no old row in INSERT trigger
For the DELETE statement, only the old is legal. Otherwise there will be an error: Error 1363 (HY000): There is no NEW row in on DELETE trigger
For UPDATE statements, new and old can be used at the same time.
2. Examples
2.1 Creating a Table
Use the table created in the article Mysqludf_json relational data in JSON encoding. Later, the user table is migrated to the NoSQL database.

mysql> CREATE TABLE ' Ttlsa_users ' (
  -> ' uid ' int (one) unsigned,
  -> ' username '-varchar ') not NULL,
  - > ' password ' varchar NOT NULL,
  -> ' createtime ' timestamp not null DEFAULT current_timestamp on UPDATE curren T_timestamp,
  -> PRIMARY KEY (' uid ')
  ->);

mysql> CREATE TABLE ' Ttlsa_users ' (
  -> ' uid ' int (one) unsigned,-> ' username '-varchar '
  ) not NULL,
  -> ' password ' varchar NOT NULL,
  -> ' createtime ' timestamp not null DEFAULT current_timestamp on UPDATE CURR Ent_timestamp,
  -> PRIMARY KEY (' uid ')
  ->);

Create another table to store the trigger action data.

mysql> CREATE TABLE ' Ttlsa_users3 ' (
  -> ' uid ' int (one) unsigned,
  -> ' userinfo ' varchar '),
  - >);

mysql> CREATE TABLE ' Ttlsa_users3 ' (
  -> ' uid ' int (one) unsigned,
  -> ' userinfo ' varchar '),
  - >);

2.2 Create trigger

 mysql> delimiter//mysql> CREATE trigger Ttlsa_users_ai-> after insert on TT Lsa_users-> for each row-> insert into TTLSA_USERS3 (UID, userinfo) VALUES (UID, json_object (New.uid, New.usern
  Ame, 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 inserts on Ttlsa_users-> for E
  Ach 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-> Updat
  E 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": 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": 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": 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": 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 Stat Ement:insert into Ttlsa_users3 (uid,userinfo) VALUES (New.uid,json_object (New.uid, New.username, New.password)) Tim Ing:after 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_ci *************************** 2. Row *************************** Trigger:ttlsa_users_au event:update table:ttlsa_users Stat Ement: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.1 Character_set_client:utf
 8 Collation_connection:utf8_general_ci Database collation:latin1_swedish_ci 2 rows in Set (0.00 sec)

Mysql> show triggers; #列出所有
mysql> show triggers from database_name, #列出数据库的触发器
mysql> show CREATE TRIGGER trigger_name;  #查看创建触发器

1. Row ***************************
        trigger:ttlsa_users_ai
       sql_mode:no_engine_substitution
SQL Original Statement:create definer= ' root ' @ ' 127.0.0.1 ' trigger ttlsa_users_ai after insert on ttlsa_users for each row insert into T TLSA_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_ci
1 row in Set ( 0.01 sec)

3.2 information_schema. Triggers table

Sql> show triggers like '%ttlsa% '; #触发器名称匹配%ttlsa%

1. Row *************************** Trigger:ttlsa_users_ai Event:insert table:ttlsa_users Stat Ement:insert into Ttlsa_users3 (uid,userinfo) VALUES (New.uid,json_object (New.uid, New.username, New.password)) Tim Ing:after 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_ci *************************** 2. Row *************************** Trigger:ttlsa_users_au event:update table:ttlsa_users Stat Ement: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.1 Character_set_client:utf
 8 Collation_connection:utf8_general_ci Database collation:latin1_swedish_ci 2 rows in Set (0.00 sec)

Mysql> show triggers; #列出所有
mysql> show triggers from database_name, #列出数据库的触发器
mysql> show CREATE TRIGGER trigger_name;  #查看创建触发器

1. Row ***************************
        trigger:ttlsa_users_ai
       sql_mode:no_engine_substitution
SQL Original Statement:create definer= ' root ' @ ' 127.0.0.1 ' trigger ttlsa_users_ai after insert on ttlsa_users for each row insert into T TLSA_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_ci
1 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:d EF 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:after Action_reference_old_table:null Action_reference_new_table:null Action_reference_old_row:old action_reference_new_row:new Created:null Sql_mode:no_engine_substit Ution definer:root@127.0.0.1 Character_set_client:utf8 collation_connection:utf8_general_ci DATABAS E_collation:latin1_swedish_ci 1 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< C11/>action_orientation:row
       action_timing:after
action_reference_old_table:null
ACTION_ 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_ci
1 row in Set (0.00 sec)

3.3 Delete triggers

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