MySQL触发器和事务

MySQL触发器和事务

触发器（Trigger）

触发器是一种自动化的数据库对象，它在执行特定的 SQL 操作（如 INSERT、UPDATE 或 DELETE）时被自动触发。它可以在表上定义，并在特定事件发生时执行自定义的 SQL 语句。

触发器的种类和时机

在实际使用中，MySQL 所支持的触发器有三种：INSERT 触发器、UPDATE 触发器和 DELETE 触发器。

• INSERT 触发器

  在 INSERT 语句执行之前或之后响应的触发器。

• UPDATE 触发器

  在 UPDATE 语句执行之前或之后响应的触发器。

• DELETE 触发器

  在 DELETE 语句执行之前或之后响应的触发器。

时机：触发器有两种时机可以触发：

• BEFORE：在事件操作之前执行触发器。
• AFTER：在事件操作之后执行触发器。
• 语法：

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
    执行语句列表
END

 
FOR EACH ROW 是告诉数据库触发器对每一行操作分别进行处理。例如，如果一次插入了多行数据，触发器会对每一行数据分别执行一次触发器中的逻辑代码。
使用场景： 当你需要根据插入、更新或删除的每一行数据进行特定操作时，需要使用 FOR EACH ROW。

NEW 和 OLD 的定义

NEW：

• NEW 用来引用触发器执行时的“新”数据，表示插入或更新操作之后的数据。
• 在 INSERT 和 UPDATE 触发器中使用。

OLD：

• OLD 用来引用触发器执行时的“旧”数据，表示更新或删除操作之前的数据。
• 在 UPDATE 和 DELETE 触发器中使用。

1. NEW 的使用：

• 在 INSERT 触发器中，NEW 表示即将插入的数据。
• 在 UPDATE 触发器中，NEW 表示更新后（即新的）数据。

2. OLD 的使用：

• 在 DELETE 触发器中，OLD 表示被删除的数据。
• 在 UPDATE 触发器中，OLD 表示更新前（即旧的）数据。

old结合delete使用例子

mysql> select * from category  where btypeid=2;
+---------+-----------+
| bTypeId | bTypeName |
+---------+-----------+
|       2 | 网站      |
+---------+-----------+
1 row in set (0.00 sec)

mysql> DELIMITER $
mysql> create trigger drop_test after delete
    -> on category for each row
    -> begin
    -> delete from books.books where btypeid=old.btypeid;
    -> end
    -> $
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> delete from category where btypeid=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from category where btype=2;
ERROR 1054 (42S22): Unknown column 'btype' in 'where clause'
mysql> select * from category where btypeid=2;
Empty set (0.00 sec)

mysql> select * from books where btypeid=2;
Empty set (0.00 sec)

old结合update使用例子

mysql> delimiter $
mysql> create trigger updata_test after update
    -> on category for each row
    -> begin
    -> update books set btypeid=new.btypeid where btypeid=old.btypeid
    -> end
    -> $

mysql> select btypeid from category where btypeid=3;
+---------+
| btypeid |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

mysql> select btypeid from books where btypeid=3;
+---------+
| btypeid |
+---------+
| 3       |
| 3       |
| 3       |
| 3       |
+---------+
4 rows in set (0.00 sec)

mysql> update category set btypeid=2 where btypeid=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select btypeid from category where btypeid=3;
Empty set (0.00 sec)

mysql> select btypeid from books where btypeid=3;
Empty set (0.00 sec)

拒绝更新表的内容

mysql> create trigger not_update_student5 before update on student5 for each row
    -> begin
    -> set new.id=old.id,new.name=old.name,new.age=old.age;
    -> end%

事务

MySQL事务是数据库管理系统提供的一种机制，用于保证一组操作的完整性和一致性。事务可以将多个数据库操作封装为一个单一的操作单元，要么全部执行，要么全部不执行，确保数据的一致性和完整性。

MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理！其他的类型不支持！

MySQL事务是数据库管理系统提供的一种机制，用于保证一组操作的完整性和一致性。事务可以将多个数据库操作封装为一个单一的操作单元，要么全部执行，要么全部不执行，确保数据的一致性和完整性。

事务的四个基本特性（ACID特性）

1. 原子性（Atomicity）

• 简单说法：要么全部做，要么全部不做。
• 举个例子：你在银行转账时，如果转账失败，你的钱不会先从你的账户扣除再失败；它要么全成功，要么全失败，没有中间状态。

2. 一致性（Consistency）

• 简单说法：事务前后，数据库的数据应该始终符合规则。
• 举个例子：比如你在转账时，银行账户的总金额应该始终保持一致。无论转账成功还是失败，数据库中的数据在开始前和结束后都应该是符合预期的，不会因为事务的进行而变得不正常。

3. 隔离性（Isolation）

• 简单说法：事务之间互不干扰。
• 举个例子：假设有两个用户同时在银行做转账操作，一个正在从A账户转100元到B账户，另一个正在从B账户转100元到C账户。隔离性确保了一个事务的操作不会影响另一个事务的结果，直到事务完全完成。

4. 持久性（Durability）

• 简单说法：一旦事务完成，数据就永远保存在数据库里。
• 举个例子：你在银行转账时，如果转账成功了，系统会把这笔钱的变动永久记录下来。如果系统崩溃了，转账依然有效，不会丢失。

用BEGIN或START TRANSACTION,ROLLBACK,COMMIT来实现事务
START TRANSACTION | BEGIN  #开启事务
COMMIT                   #提交当前事务，执行永久操作。
ROLLBACK       #回滚当前事务到开始点，取消上一次开始点后的所有操作。
 
MYSQL默认是自动提交的，也就是你提交一个SQL QUERY，它就直接执行！
SET AUTOCOMMIT = {0 | 1} 设置事务是否自动提交，默认是自动提交的。
 0：禁止自动提交
 1：开启自动提交。

事务的使用示例

假设我们有一个银行转账的场景，用户A将100元转账给用户B。

-- 开始事务
START TRANSACTION;

-- 用户A账户扣款 100
UPDATE accounts SET balance = balance - 100 WHERE user_id = 'A';

-- 用户B账户加款 100
UPDATE accounts SET balance = balance + 100 WHERE user_id = 'B';

-- 如果没有错误，提交事务
COMMIT;
-- 如果在这两个操作之间发生了错误，例如，扣款操作成功，但加款操作失败，则可以通过回滚操作来撤销前面已经完成的操作：
ROLLBACK;

你遇到的情况是，虽然你执行了 ROLLBACK，但是数据并没有回滚。原因在于你执行的操作实际上已经被提交了，而在 COMMIT 之后执行的 ROLLBACK 是无法撤销已经提交的事务的。

如果想要回滚就不能加上COMMIT;