Home>

0. Basic concepts of triggersTriggers are a special stored procedure.It's inserting,Triggered when data in a specific table is deleted or modified,It has finer and more complex data control capabilities than the standard functions of the database itself.

Database triggers have the following functions:

(1). Security. You can give users some rights to manipulate the database based on the values ​​of the database.

#Can restrict user operations based on time,For example, it is not allowed to modify the database data after work and holidays.

#Can restrict user operations based on data in the database,For example, the price of a stock is not allowed to rise more than 10%at a time.

(2). Auditing. You can track user operations on the database.

#Audit the statement of the user to operate the database.

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

(3). Implementing complex data integrity rules

#Implement non-standard data integrity checks and constraints.Triggers can create more complex restrictions than rules.Unlike rules,Triggers can reference columns or database objects.For example, a trigger can roll back any futures attempting to eat more than its own margin.

#Provide a variable default value.

(4). Implement complex non-standard database-related integrity rules.Triggers can make serial updates to related tables in the database.For example, a delete trigger on the author_code column of the auths table can cause corresponding rows in other tables to be deleted.

#When modifying or deleting, cascade modify or delete matching rows in other tables.

#When modifying or deleting, set the matching rows in other tables to null values.

#Set the matching rows in other tables to the default value when modifying or deleting.

#Triggers can reject or roll back changes that disrupt related integrity,Cancel a transaction attempting a data update.When inserting a foreign key that does not match its primary key,This trigger will work.For example, you could generate an insert trigger on 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). Automatic calculation of data values,If the value of the data meets certain requirements,Specific processing is performed.For example, if the funds on the company's account are less than 50,000 yuan, a warning data is immediately sent to the finance staff.

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}

Explanation of relevant parts of the grammar:

1.1 Authorization and recyclingCreate trigger requires 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`;

Withdrawal of authority:

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_nameA trigger command must be given,Up to 64 characters, it is recommended to use the table name_trigger type abbreviation method.Such as ttlsa_posts_bi (table ttlsa_posts, the trigger occurs before the insert)

1.3 definer clauseWhen the trigger is activated,Check accessEnsure that triggers are used safely.

1.4 trigger_timeDefine when the trigger fires.Can be set to occur before or after line record changes.

1.5 trigger_eventDefine the trigger event.The events that are triggered are:

1.5.1insert:Fired 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 will not trigger the trigger,Because they are not using delete. Similarly, deleting a partitioned table will not trigger.

There is a potentially confusing situation,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.For example, you cannot create two before update triggers for a table, but you can create a before update and a before insert or a before update and an after update trigger.

1.6 for each row clauseDefine the trigger execution interval.The for each row clause defines that the trigger performs an action every other row,Rather than once for the entire table.

1.7 trigger_body clauseContains the SQL statement to trigger execution. Can be any legal statement,Including compound statements (requires begin ... end structure), flow control statements (if, case, while, loop, for, repeat, leave, iterate), variable declaration (declare) and assignment (set), exception handling declarationAllow conditional claims,But the statement here is subject to the same restrictions as the function.

1.7.1 old and newIn the SQL statement of the trigger, you can associate any column in the table,Identified by using the old and new column names,Such as old.col_name, new.col_name. old.col_name is the value associated with a column of an existing row before it was updated or deleted.new.col_name associates a new row with the value of inserting or updating a column of an existing row.

For insert statements, only new is legal.Otherwise it will report an error:error 1363 (hy000):there is no old row in on insert trigger

For delete statements, only old is legal.Otherwise it will report 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 Create TableUse the table created in the article "mysqludf_jsonEncoding Relational Data with json".Subsequent migration of user tables to the nosql database.

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 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 matches%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:[email protected]
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
      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:[email protected]
character_set_client:utf8
collation_connection:utf8_general_ci
 database collation:latin1_swedish_ci
2 rows in set (0.00 sec)
mysql>show triggers;#list all
mysql>show triggers from database_name;#list the triggers of the database
mysql>show create trigger trigger_name;#View create trigger
*************************** 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 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_ci
1 row in set (0.01 sec)
</pre>
<p>
<strong>
3.2 information_schema.triggers table
</strong>
</p>
</div>
<div>
<pre>
sql>show triggers like "%ttlsa%";#Trigger name matches%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:[email protected]
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
      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:[email protected]
character_set_client:utf8
collation_connection:utf8_general_ci
 database collation:latin1_swedish_ci
2 rows in set (0.00 sec)
mysql>show triggers;#list all
mysql>show triggers from database_name;#list the triggers of the database
mysql>show create trigger trigger_name;#View create trigger
*************************** 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 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_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: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: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:[email protected]
   character_set_client:utf8
   collation_connection:utf8_general_ci
    database_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
    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:[email protected]
   character_set_client:utf8
   collation_connection:utf8_general_ci
    database_collation:latin1_swedish_ci
1 row in set (0.00 sec)

3.3 delete trigger

mysql>drop trigger trigger_name;
mysql>drop trigger trigger_name;
  • Previous Detailed JavaScript basic types and reference types
  • Next A complete example of the progress bar effect displayed at the top of the page with jQuery