数据库实践LAB大纲 04 触发器

最新推荐文章于 2025-12-26 18:13:00 发布

原创最新推荐文章于 2025-12-26 18:13:00 发布 · 549 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #mysql

DataBase 专栏收录该内容

25 篇文章

订阅专栏

游标是数据库系统提供的一种机制，用于逐条处理查询结果集，常用于循环处理。而触发器则是在特定数据库操作（如INSERT,UPDATE,DELETE）发生时自动执行的一系列操作，用于增强数据的安全性和完整性，例如限制字段值范围。在MySQL中，触发器分为BEFORE和AFTER两种，可用于实现复杂的数据验证和约束。然而，触发器可能导致批量操作性能下降，并且在MyISAM存储引擎中不保证原子性，InnoDB则支持事务的原子性。

游标

系统为用户开设的一个数据缓冲区 —— 存T-SQL语句从数据库检索出来的结果集

对结果集处理：结果集一条条提取记录，这时要用游标

使用

利用基于变量的select into语句，只能处理单条记录
使用游标循环处理

声明游标： DECLARE cursor_name CURSOR FOR select_statement
打开游标：OPEN cursor_name
使用游标：FETCH cursor_name INTO var_name [, var_name] …
关闭游标：CLOSE cursor_name

顺序

游标声明在处理程序之前，在变量和条件之后
select 语句不能出现INTO语句

特性

允许应用程序对查询语句 select 返回结果集“某一行”进行操作，rather than一次对整体做同一个操作
只读：不可能新基础表数据
不可滚动：按确定顺序获取行，不能以相反顺序获取，不可跳行
敏感：敏感游标指向实际数据（类似指针，引用），不敏感游标使用临时副本（类似传参）
- 前者比后者更快，因为不需要copy临时数据
- 但其他数据做的更改会影响前者的数据。

create procedure qq_cursor_insert()
begin
  declare a varchar(15);
  declare b int;
  declare cur1 cursor for select qqno, tid from qq;
  declare exit handler for not found close cur1;

  open cur1;
  repeat
    fetch cur1 into a, b;
    insert into new_qq(a, b) values(a, b);
  until 0
  end repeatl;
  close cur1;
end;

触发器

定义了一系列操作，这一系列操作称为触发程序，当触发事件发生时，触发程序会自动运行

主要监视以下情况

insert
update
delete

行驶时间可设置为

before
after

特点

安全性：使users具有操作数据库的某种权利，比如基于时间限制，规定时间外不能修改；比如基于数据限制，不允许分数为负数
审计：跟踪user对数据库的操作
复杂的数据完整性规则: 完整性检查和约束，如触发器可回退任何企图吃进超过自己保证金的期货
复杂的非标准的数据库相关完整性规则： cascade 修改或删除匹配行/设置null或default/拒绝、回退破坏相关完整性的变化

创建

触发程序是与表有关的命名数据库对象，创建后该对象在MySQL中

语法

create trigger trigger_name trigger_time trigger_event
on tbl_name for each row trigger_stmt

trigger_name：名字，唯一名称
trigger_time ：触发器被触发的时间 BEFORE（验证新数据是否满足使用的限制） | AFTER（激活触发器的语句执行之后完成几个或更多的改变）
trigger_event ：激活触发器的语句的类型
tbl_name：与触发器相关联的表名，不能为TEMPORARY表或VIEW（同一个表不能拥有两个具有相同触发时刻和事件的触发器，可能其他版本会有差异）
for each row：用来指定对于受触发事件影响的每一行都要激活触发器的动作
trigger_stmt：是当触发程序激活时执行的语句。多个语句用 BEGIN END结构

example – 限制某些字段在 60， 150， 230内

delimiter $$
create trigger course_insert_before_trigger before insert
begin
    if(new.up_limit = 60 || new.up_limit = 150 )
        then set new.up_limit = new.up_limit;
    else 
        insert into mytable values(0); 
        -- 注意 mytable并不存在于数据库中
        --原理是创造一个error终止这次insert
    end if
end;
delimiter ;

OLD和NEW访问受trigger影响的字段值

INSERT 只能用 NEW
DELETE 只能用 OLD
UPDATE，可以用OLD引用更新前的值，也可能用 NEW引用更新后的值

OLD只读，不可改

example – update触发器限制某些字段在 60， 150， 230内

delimiter $$
create trigger course_update_before_trigger before update
on course for each row 
begin
    if(new.up_limit!=60||new.up_limit!=150||new.up_limit!=230) 
        then set new.up_limit=old.up_limit;
end if;
end;
$$
delimiter ;

查看触发器

show triggers 查看当前数据库中所有触发器的信息
select *from information_schema.triggers
触发器的定义都存放在information_schema数据库下的triggers表中
show create trigger 检查特定触发器定义
创建触发器后，MySQL自动在数据库目标下创建TRN以及TRG触发器文件，可以自行打开

删除触发器

drop trigger [schema_name.]trigger_name

schema_name.：可选项指定数据库
trigger_name 触发器
drop trigger要super权限
删除一个表的同时，也会自动删除表上的触发器，trigger不可更新覆盖

注意事项

创建

trigger内部select语句不能返回结果集
同一个表不能创建两个相同触发时间、触发事件的触发程序（5.7.2版本之后可以）
不可使用事务相关语句

start transaction
commit
rollback
set autocommit=0

MySQL触发器针对记录进行操作，当批量更新数据时，引入触发器会导致批量更新操作的性能降低

引擎执行的过程

MySQL存储引擎 MyISAM中， trigger不能保证原子性

更新表的同时，trigger实现另一个表的更新，若trigger程序失败了，不会回滚这次更新前一个表的操作。
InnoDB引擎支持十五，trigger保持更新操作和trigger操作的原子性 —— 把他们视为同一个transaction

InnoDB存储引擎实现外键约束关系时，建议使用级联选项维护外键数据 —— 先维护子表数据，再维护父表

触发器的使用过程

trigger内部不能使用update（用set代替）
before处罚程序中：auto_increament字段的new值为0 instead of 实际插入新纪录时自动生成的自增型字段值

例子

维护冗余数据 —— 避免数据不一致问题的发生
模拟外键级联选项

对于InnoDB存储引擎的表而言，由于支持外键约束，在定义外键约束时，通过设置外键的级联选项cascade、set null或者no action（restrict），外键约束关系可以交由InnoDB存储引擎自动维护
如果InnoDB存储引擎的表之间存在外键约束关系，但是不存在级联选项或者使用数据库表为MyISAM（该表不支持外键约束关系），则可以使用触发器模拟实现“外键约束”之间的“级联选项

游标