mysql 触发器与事务

IT_CODE.

已于 2022-05-12 15:05:52 修改

阅读量968

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

于 2022-05-05 16:56:07 首次发布

本文链接：https://blog.csdn.net/qq_54250695/article/details/124587296

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

一、什么是触发器

在数据表中发生了某件事（插入、删除、更新操作），然后自动触发了预先编好的若干条SQL语句的执行。

二、触发器的特点及作用

特点：触发事件的操作和触发器里面的SQL语句是一个事物操作，具有原子性，要么全部执行，要么都不执行。比如：银行转账。
作用：保证数据的完整性，起到约束作用。

三、创建触发器语句

DELIMITER //        -- 修改结束符号
CREATE  TRIGGER    xxx    -- 触发器的名字
AFTER  INSERT  ON   biao  --  作用在那个表上，以及针对insert，update,delete
FOR EACH  ROW           --  每一行记录都起作用
BEGIN   
            --    写入提前编译要执行的sql语句                                         
    #将新添加到emps表中的记录添加到 emp_back中
    INSERT INTO  emps_back(employee_id,last_name,salary) VALUES
     (NEW.employee_id,NEW.last_name,NEW.SALARY);

END //
DELIMITER ;  --改回原来的结束符

四、查看存储器

SHOW TRIGGERS;
SHOW CREATE TRIGGER trigger_name;
SELECT  *   FROM    information_schema.triggers;

五、NEW和OLD的应用

MySQL 中定义了 NEW 和 OLD 两个临时表，用来表示触发器的所在表中，触发了触发器的哪一行数据，来引用触发器中发生变化的记录内容，具体地：

1. 在 INSERT 型触发器中，NEW 用来拦截并保存将要（BEFORE）或已经（AFTER）插入的新数据；

例题： ‘向表中添加数据的同时，同步添加到另外一张表中’

创建触发器,emps_insert_trigger,每当向emps表中添加一条记录时, 同步将这条记录添加emp_back表中

DELIMITER //
CREATE  TRIGGER  emps_insert_trigger 
AFTER  INSERT  ON   emps
FOR EACH  ROW
BEGIN
    #将新添加到emps表中的记录添加到 emp_back中
    INSERT INTO  emps_back(employee_id,last_name,salary) VALUES
     (NEW.employee_id,NEW.last_name,NEW.SALARY);

END //
DELIMITER ;


--  测试触发器


-- 向 emps表中添加记录，看是否emp_back中是否也添加记录
INSERT INTO   emps  VALUES(300,'新来的',3400);

--  查看结果
SELECT  *  FROM  emps_back;


mysql> SELECT  *  FROM  emps_back;
+-------------+-----------+---------+
| employee_id | last_name | salary  |
+-------------+-----------+---------+
|         300 | 新来的    | 3400.00 |

2. 在 UPDATE 型触发器中，OLD用来拦截并保存将要或已经被修改的原数据，NEW 用来拦截并保存将要或已经修改为的新数据。

例题:

当结束证号修改了，触发器就将改前的借书证号与修改后的借书证号插入到readerrecord表中

DELIMITER $$
CREATE TRIGGER update_reader
AFTER UPDATE
ON reader
FOR  EACH  ROW
BEGIN
INSERT INTO   readerrecord VALUES (OLD.借书证号,NEW.借书证号,NEW.操作员);
END $$

DELIMITER ;





--  测试触发器


mysql> UPDATE   reader  SET 借书证号="120" WHERE 部门="物理系";
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1  Changed: 0  Warnings: 0

--  查看结果

mysql> select*  from readerrecord;
+--------+--------+-------------+
| old_id | new_id | operation   |
+--------+--------+-------------+
|    111 |    120 | 01王贤达    |

3 在 DELETE 型触发器中，OLD 用来拦截并保存将要或已经被删除的原数据。

例题: '备份数据'

每向emps表中中删除一条记录时,同步将删除的这条记录添加emps_back1表中

DELIMITER $
CREATE   TRIGGER      emps_del_trigger   
BEFORE  DELETE   ON   emps  --  用before  比较好，提前备份
FOR  EACH ROW   -- 每一行
BEGIN
   --  每向emps表中中删除一条记录时,同步将删除的这条记录添加emps_back1表中
   --  备份 
    INSERT INTO  emps_back1(employee_id,last_name,salary) VALUES
     (OLD.employee_id,OLD.last_name,OLD.SALARY);
END $
DELIMITER ;


--  测试触发器
DELETE   FROM   emps  WHERE  employee_id = 101;

-- 查看有没有备份成功
SELECT  *  FROM   emps_back1;

mysql> SELECT  *  FROM   emps_back1;
+-------------+-----------+----------+
| employee_id | last_name | salary   |
+-------------+-----------+----------+
|         101 | Kochhar   | 13747.01 |
+-------------+-----------+----------+

事务

事务的原子性与一致性

-- 事务
-- 事务用于保证数据的一致性，由一组组的dml语句组成，改组数据要么
-- 全部成功，要么全部失败如:转账就要用事务来处理，保证的数据的一致性

-- start transaction --开始一个事务

-- savepoint 保存点名 --设置保存点
-- rollback to 保存点名 -- 回退事务
-- rollback -- 回退全部事务
-- commit -- 提交事务，所有的操作生效，不能回退

-- 回滚一定要 1.开启事务 2.设置保存点

事务操作的示意图

 --   演示事务的操作
 
    CREATE  TABLE   t15 (
            id  INT,
            `name` VARCHAR(32)    
    );
    
    --  1开启一个事务
    START   TRANSACTION  
    --  2.设置一个保存点    --   得先设置保存点，再进行dml操作
      SAVEPOINT  a
      --  执行 dml操作
      INSERT  INTO  t15 VALUES (100,'tom');
      SELECT  *   FROM  t15;  
      
      --  到了11  点(第二个阶段)  -- 第二个保存点
      SAVEPOINT  b
      
      --   再次执行dml操作 
      INSERT  INTO   t15  VALUES(200,'jack');
      
         --  回退到11点
         ROLLBACK  TO b
         SELECT  *   FROM  t15; 
          -- 回退到10点
          ROLLBACK TO a   --  注意如果往a回滚了，因为a这个保存点在b 之前，所以  SAVEPOINT b does not exist
          
          -- 全部回退，也就是最早的那个保存点a
          ROLLBACK        
         --  到了12 点  提交了commit事务  ,回滚不了  
         -- 提交(都回滚不了)
         COMMIT   --  SAVEPOINT a does not exist，SAVEPOINT b does not exist

事务使用场景:

执行结果:

START   TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>            SAVEPOINT  a;
Query OK, 0 rows affected (0.00 sec)

mysql>          UPDATE  blance  SET sal = sal-100 WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>                   SAVEPOINT  b;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>          UPDATE  blance SET sal = sal+100 WHERE id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>           COMMIT;
Query OK, 0 rows affected (0.01 sec)

事务注意事项:

-- 1. 如果不开启事务，默认情况下，cml操作时自动提交的，不能回滚
-- 2.如果开启了一个事务，你没有创建保存点，你可以执行rollback

--3你可以在这个事务(还没有提交时) 创建多个保存点.比如 savapoint

-- 4.你可以在事务没有提前前，选择回退到哪个保存点
-- 5.mysql的事务机制需要innodb的储存引擎才可以使用，myisam不好使

学习笔记记录一下，方便温习。