Correct use of MySQL triggers and case analysis

The following articles mainly describe the detailed usage and case analysis of MySQL triggers. At the same time, this article also lists some code in the actual operations of MySQL triggers, the following is a detailed description of the article.

Trigger case

 
 

  
  
mysql> select * from a; +------+------+------+ 
| id | name | age | +------+------+------+ 
| 1 | A1 | 10 | | 2 | A2 | 20 | +------+------+------+ 
mysql> select * from b; +------+------+------+ 
| rid | id | age | +------+------+------+ | 5 | 2 | 20 | +------+------+------+  
 
 

We hope that when the age field of Table a is updated, the corresponding age field of Table B is also updated:

For example:

 
 

  
  
update a set ageage=age+1 where id=2;  
 
 

The related table is changed:

 
 

  
  
mysql> select * from a; +------+------+------+ 
| id | name | age | +------+------+------+ 
| 1 | A1 | 10 | | 2 | A2 | 21 | +------+------+------+ 
mysql> select * from b; +------+------+------+ 
| rid | id | age | +------+------+------+ | 5 | 2 | 21 | +------+------+------+ 
 
 

Correct writing

Trigger code

 
 

  
  
CREATE TRIGGER bbs1 AFTER UPDATE ON a FOR EACH ROW update b set age=NEW.age where id=NEW.id;  
 
 

MySQL triggers

Trigger concept: "program code automatically executed in the database to respond to certain events in a special table ." (Wikipedia) is a piece of code that is automatically activated when a special database event, such as INSERT or DELETE occurs. Triggers can be easily used for logging, automatic "stacked" changes to a single table to other linked tables, or automatic updates to table relationships.

When a new integer is added to the database domain, the code segment that automatically updates the total number of operations is a trigger. The SQL command block that automatically records changes to a special database table is also a trigger instance.

The trigger is a new feature of MySQL 5.x. With the emergence of a new version of the 5.x code tree, this feature is gradually improved. In this article, I will briefly introduce how to define and use a trigger, view the trigger status, and delete the trigger after use. I will also show you an application instance of a trigger in the real world and verify its changes to database records.

Example

Simple, though artificial) instances are used to demonstrate the best way to understand the MySQL trigger application. First, we create two single-domain tables. Name List in one table: Table Name: data), number of characters inserted in the other table name: chars ). I want to define a trigger in the data table. Each time a new name is inserted into the trigger, the total number of running tasks in the chars table is automatically updated based on the number of characters in the new inserted records.

 
 

  
  
mysql> CREATE TABLE data (name VARCHAR(255)); 
Query OK, 0 rows affected (0.09 sec) mysql> CREATE TABLE chars (count INT(10)); 
Query OK, 0 rows affected (0.07 sec) mysql> INSERT INTO chars (count) VALUES (0); 
Query OK, 1 row affected (0.00 sec) mysql> CREATE TRIGGER t1 
AFTER INSERT ON data FOR EACH ROW UPDATE chars SET countcount = count + CHAR_LENGTH(NEW.name); Query OK, 0 rows affected (0.01 sec)  
 
 
 

The key to understanding the above Code is the create trigger command, which is used to define a new TRIGGER. This command creates a new trigger, assuming the name is t1. Every time a new record is inserted into the data table, t1 is activated.

There are two important clauses in this trigger:

The after insert clause indicates that the trigger is activated AFTER the new record is inserted into the data table.

The UPDATE chars SET count = count + CHAR_LENGTH (NEW. name) clause indicates the SQL command executed after the trigger is activated. In this example, this command updates the chars. count column with the number of characters in the newly inserted data. name field. This information can be obtained through the built-in MySQL function CHAR_LENGTH.

The NEW Keyword placed before the source table domain name is also worth noting. This keyword indicates that the trigger should consider the new value of the domain, that is, the value just inserted into the domain ). MySQL also supports the corresponding OLD prefix, which can be used to indicate the previous values of the domain.

You can call the show trigger command to check whether the TRIGGER is activated.

 
 

  
  
mysql> SHOW TRIGGERS; *************************** 
1. row *************************** 
?Trigger: t1 ?Event: INSERT ?Table: data Statement: 
UPDATE chars SET countcount = count + CHAR_LENGTH(NEW.name) 
Timing: AFTER ?Created: NULL ql_mode: 1 row in set (0.01 sec)  
 
 

Activate the trigger and start testing it. Try to insert several records in the data table:

 
 

  
  
mysql> INSERT INTO data (name) VALUES ('Sue'), ('Jane'); 
Query OK, 2 rows affected (0.00 sec) Records: 2?Duplicates: 0?Warnings: 0 
 
 

Then check the chars table to see if the MySQL trigger has completed the task:

 
 

  
  
mysql> SELECT * FROM chars; +-------+ 
| count | +-------+ | 7| +-------+ 1 row in set (0.00 sec) 
 
 

As you can see, the INSERT command in the data table activates the trigger, which calculates the number of characters inserted records and stores the results in the chars table. If you add another record to the data table, the chars. count value will also increase accordingly.

After the TRIGGER is applied, you can use the drop trigger command to easily delete it.

 
 

  
  
mysql> DROP TRIGGER t1; Query OK, 0 rows affected (0.00 sec) 
 
 

Note: Ideally, you also need an inverted trigger. Each time a record is deleted from the source table, it is subtracted from the total number of characters. This is easy to do. You can use it as an exercise. Tip: using the before delete on clause is one of the methods.

Self-write (tested)

 
 

  
  
mysql> create trigger t2 before delete on 
data for each row update chars set countcount=count-char_length(old.name); 
Query OK, 0 rows affected (0.03 sec) 
 
 

Now, I want to create an audit record to track changes to this table. This record will reflect every change in the table and show the user who made the change and the time of the change. I need to create a new table to store this information table name: audit), as shown below. List C)

List C

 
 

  
  
mysql> CREATE TABLE audit (id INT(7), balance FLOAT, 
user VARCHAR(50) NOT NULL, time TIMESTAMP NOT NULL); 
Query OK, 0 rows affected (0.09 sec) 
mysql> create table accounts(id int(7),label VARCHAR(45),balance float); 
 
 

Next, I will define a MySQL trigger in the accounts table. List D)

List D

 
 

  
  
mysql> CREATE TRIGGER t3 AFTER UPDATE ON accounts 
FOR EACH ROW INSERT INTO audit (id, balance, user, time) 
VALUES (OLD.id, NEW.balance, CURRENT_USER(), NOW()); 
Query OK, 0 rows affected (0.04 sec) 
 
 

If you have reached this step, it is easy to understand. Each time an UPDATE is performed in the accounts table, the trigger inserts an INSERT Statement), the id of the corresponding record, the new balance, the current time, and the name of the user logging on to the audit table.

Example in implementation: audit records with triggers

Now that you understand the basic principles of triggers, let's look at a slightly complicated example. We often use triggers to create an automatic "audit record" to record various user changes to the database. To understand the actual application of audit records, see the following table name: accounts), which lists the three bank account balances of a user. Table)

Table

 
 

  
  
mysql> SELECT * FROM accounts; +----+------------+---------+ 
| id | label| balance | +----+------------+---------+ 
|1 | Savings #1 |500 | |2 | Current #1 |2000 | |3 | 
Current #2 |3500 | +----+------------+---------+ 3 rows in set (0.00 sec) 
 
 

Then, check whether the trigger is activated:

 
 

  
  
mysql> SHOW TRIGGERS ; *************************** 
1. row *************************** ?Trigger: t1 ?Event: 
UPDATE ?Table: accounts Statement: INSERT INTO audit (id, balance, user, time) 
VALUES (OLD.id, NEW.balance, CURRENT_USER(), NOW()) Timing: AFTER ?Created: NULL Sql_mode: 1 row in set (0.01 sec) 
 
 

Let's look at the final result list E ):

List E

 
 

  
  
mysql> UPDATE accounts SET balance = 500 WHERE id = 
1; Query OK, 1 row affected (0.00 sec) Rows matched: 
1?Changed: 1?Warnings: 0 mysql> UPDATE accounts SET 
balance = 900 WHERE id = 3; Query OK, 1 row affected 
(0.01 sec) Rows matched: 1?Changed: 1?Warnings: 0 mysql> 
UPDATE accounts SET balance = 1900 WHERE id = 1; Query OK, 
1 row affected (0.00 sec) Rows matched: 1?Changed: 1?Warnings: 0  
 
 

Note that the changes made to the accounts table have been recorded in the audit table. In the future, if there is a problem, we can easily restore it.

 
 

  
  
mysql> SELECT * FROM audit; +------+---------+----------------+---------------------+ 
| id| balance | user| time| +------+---------+----------------+---------------------+ 
|1 |500 | root@localhost | 2006-04-22 12:52:15 | |3 |900 | root@localhost | 2006-04-22 12:53:15 
| |1 |1900 | root@localhost | 2006-04-22 12:53:23 | +------+---------+----------------+---------------------+ 3 rows in set (0.00 sec)  
 
 

As shown in the preceding example, the MySQL trigger is a powerful new function that greatly enhances the automation of RDBMS. Test it on your own and practice it!