How to apply triggers and transactions in MySQL database

1. Trigger

Concept

Trigger is a method provided by SQL server to programmers and data analysts to ensure data integrity. It is related to table events. Special stored procedures are triggered by events. Triggers are often used to enforce data integrity constraints and business rules.

2. Trigger operations

Create account and account_log data tables:

CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),			
	money DOUBL
);
INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);
CREATE TABLE account_log(
	id INT PRIMARY KEY AUTO_INCREMENT,
	operation VARCHAR(20),			
	operation_time DATETIME,		
	operation_id INT,			
	operation_params VARCHAR(200)      
);

Add, delete, and modify operations of triggers

// 语法格式： BEFORE|AFTER    INSERT: 插入 UPDATE：更新  DELETE：删除
// DELIMITER $
// CREATE TRIGGER 触发器名称
// BEFORE|AFTER INSERT|UPDATE|DELETE
// ON 表名
// FOR EACH ROW
// BEGIN
// 	触发器要执行的功能;
// END$
// DELIMITER ;


// 创建INSERT型触发器。用于对account表新增数据进行日志的记录
DELIMITER $

CREATE TRIGGER account_insert
AFTER INSERT
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('插入后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END $

DELIMITER ;

// 向account表添加一条记录
INSERT INTO account VALUES (NULL, '王五', 2000);

// 查询account表
SELECT * FROM account;

View and delete operations of triggers

// 查看标准语法： SHOW TRIGGERS;
// 查看触发器
SHOW TRIGGERS;


// 删除标准语法：DROP TRIGGER 触发器名称;
// 删除account_delete触发器
DROP TRIGGER account_delete;

3. Transaction

Concept

In a relational database, a transaction can be a SQL statement, a group of SQL statements or an entire program . Its characteristic is that this unit either succeeds or fails at the same time, and each SQL statement in the unit depends on each other to form a whole. Transactions should have four attributes: atomicity, consistency, isolation, and durability. These four properties are often called ACID properties.

Four major characteristics:

• Atomicity: A transaction is an indivisible unit of work. The transaction contains All operations either succeed or fail and are rolled back.

• Consistency: Transactions must cause the database to change from one consistency state to another consistency state, that is to say, a transaction must be executed before and after execution. in a consistent state.

• Isolation: When multiple users access the database concurrently, such as when operating the same table, the transaction opened by the database for each user cannot be used by other transactions Interference by operations, multiple concurrent transactions must be isolated from each other

• Persistence: Once a transaction is submitted, changes to the data in the database It is permanent, and the operation of committing the transaction will not be lost even if the database system encounters a failure.

4. Transaction operation

Basic process

• ##Open transaction:Record rollback point , and notify the server

• Execute the SQL statement:Execute one or more specific sql statements

• End transaction (submit|rollback): Submit: No problem occurs, the data is updated; Rollback: A problem occurs, the data is restored to the state when the transaction was started

Basic operations

-- 开启事务
START TRANSACTION;

-- 执行SQL语句
UPDATE account SET money=money-500 WHERE NAME='张三';

-- 回滚事务(出现问题)
ROLLBACK;

-- 提交事务(没出现问题)
COMMIT;

Transaction submission method

// 查看提交方式
SELECT @@AUTOCOMMIT;  -- 1代表自动提交    0代表手动提交
// 修改事务的提交方式
SET @@autocommit=1;

Transaction isolation level

Four isolation levels

• Read uncommitted: read uncommitted

• Read committed: read committed (Oracle/SQL Server database default level )

• Repeatable read: repeatable read (MySQL default level)

• Serialization:serializable

may cause problems:

• Dirty read: refers to a Data in another uncommitted transaction was read during transaction processing, resulting in inconsistent query results.

• Non-repeatable read: refers to a transaction During the processing, data modified and submitted in another transaction was read, resulting in inconsistent query results.

• Phantom reading: select whether a certain record exists, The record does not exist. This record was prepared to be inserted, but when the insert was executed, it was found that the record already existed and could not be inserted. Or execute delete deletion, but find that the deletion is successful

Isolation operation

// 查询数据库隔离级别
SELECT @@TX_ISOLATION;

// 修改数据库隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

Note:The isolation level becomes more and more secure from small to large High, but the efficiency is getting lower and lower, so it is not recommended to use READ UNCOMMITTED and SERIALIZABLE isolation levels.

The above is the detailed content of How to apply triggers and transactions in MySQL database. For more information, please follow other related articles on the PHP Chinese website!