search
HomeDatabaseMysql TutorialHow 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!

Statement
This article is reproduced at:亿速云. If there is any infringement, please contact admin@php.cn delete
How does MySQL differ from SQLite?How does MySQL differ from SQLite?Apr 24, 2025 am 12:12 AM

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

What are indexes in MySQL, and how do they improve performance?What are indexes in MySQL, and how do they improve performance?Apr 24, 2025 am 12:09 AM

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Explain how to use transactions in MySQL to ensure data consistency.Explain how to use transactions in MySQL to ensure data consistency.Apr 24, 2025 am 12:09 AM

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.

In what scenarios might you choose PostgreSQL over MySQL?In what scenarios might you choose PostgreSQL over MySQL?Apr 24, 2025 am 12:07 AM

Scenarios where PostgreSQL is chosen instead of MySQL include: 1) complex queries and advanced SQL functions, 2) strict data integrity and ACID compliance, 3) advanced spatial functions are required, and 4) high performance is required when processing large data sets. PostgreSQL performs well in these aspects and is suitable for projects that require complex data processing and high data integrity.

How can you secure a MySQL database?How can you secure a MySQL database?Apr 24, 2025 am 12:04 AM

The security of MySQL database can be achieved through the following measures: 1. User permission management: Strictly control access rights through CREATEUSER and GRANT commands. 2. Encrypted transmission: Configure SSL/TLS to ensure data transmission security. 3. Database backup and recovery: Use mysqldump or mysqlpump to regularly backup data. 4. Advanced security policy: Use a firewall to restrict access and enable audit logging operations. 5. Performance optimization and best practices: Take into account both safety and performance through indexing and query optimization and regular maintenance.

What are some tools you can use to monitor MySQL performance?What are some tools you can use to monitor MySQL performance?Apr 23, 2025 am 12:21 AM

How to effectively monitor MySQL performance? Use tools such as mysqladmin, SHOWGLOBALSTATUS, PerconaMonitoring and Management (PMM), and MySQL EnterpriseMonitor. 1. Use mysqladmin to view the number of connections. 2. Use SHOWGLOBALSTATUS to view the query number. 3.PMM provides detailed performance data and graphical interface. 4.MySQLEnterpriseMonitor provides rich monitoring functions and alarm mechanisms.

How does MySQL differ from SQL Server?How does MySQL differ from SQL Server?Apr 23, 2025 am 12:20 AM

The difference between MySQL and SQLServer is: 1) MySQL is open source and suitable for web and embedded systems, 2) SQLServer is a commercial product of Microsoft and is suitable for enterprise-level applications. There are significant differences between the two in storage engine, performance optimization and application scenarios. When choosing, you need to consider project size and future scalability.

In what scenarios might you choose SQL Server over MySQL?In what scenarios might you choose SQL Server over MySQL?Apr 23, 2025 am 12:20 AM

In enterprise-level application scenarios that require high availability, advanced security and good integration, SQLServer should be chosen instead of MySQL. 1) SQLServer provides enterprise-level features such as high availability and advanced security. 2) It is closely integrated with Microsoft ecosystems such as VisualStudio and PowerBI. 3) SQLServer performs excellent in performance optimization and supports memory-optimized tables and column storage indexes.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!