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

游标

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

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

使用

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

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

顺序

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

特性

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

create procedure qq_cursor_insert()
begindeclare 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;repeatfetch cur1 into a, b;insert into new_qq(a, b) values(a, b);until 0end repeatl;close cur1;
end;

触发器

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

主要监视以下情况

1. insert
2. update
3. delete

行驶时间可设置为

1. before
2. after

特点

1. 安全性：使users具有操作数据库的某种权利，比如基于时间限制，规定时间外不能修改；比如基于数据限制，不允许分数为负数
2. 审计： 跟踪user对数据库的操作
3. 复杂的数据完整性规则: 完整性检查和约束，如触发器可回退 任何企图吃进超过自己保证金的期货
4. 复杂的非标准的数据库相关完整性规则： 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
beginif(new.up_limit = 60 || new.up_limit = 150 )then set new.up_limit = new.up_limit;else insert into mytable values(0); -- 注意 mytable并不存在于数据库中--原理是创造一个error终止这次insertend if
end;
delimiter ;

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

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

• OLD只读，不可改

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

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

查看触发器

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

删除触发器

drop trigger [schema_name.]trigger_name

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

注意事项

创建

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

• start transaction
• commit
• rollback
• set autocommit=0

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

引擎执行的过程

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

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

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

触发器的使用过程

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

例子

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

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

游标

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

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

使用

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

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

顺序

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

特性

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

create procedure qq_cursor_insert()
begindeclare 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;repeatfetch cur1 into a, b;insert into new_qq(a, b) values(a, b);until 0end repeatl;close cur1;
end;

触发器

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

主要监视以下情况

1. insert
2. update
3. delete

行驶时间可设置为

1. before
2. after

特点

1. 安全性：使users具有操作数据库的某种权利，比如基于时间限制，规定时间外不能修改；比如基于数据限制，不允许分数为负数
2. 审计： 跟踪user对数据库的操作
3. 复杂的数据完整性规则: 完整性检查和约束，如触发器可回退 任何企图吃进超过自己保证金的期货
4. 复杂的非标准的数据库相关完整性规则： 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
beginif(new.up_limit = 60 || new.up_limit = 150 )then set new.up_limit = new.up_limit;else insert into mytable values(0); -- 注意 mytable并不存在于数据库中--原理是创造一个error终止这次insertend if
end;
delimiter ;

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

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

• OLD只读，不可改

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

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

查看触发器

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

删除触发器

drop trigger [schema_name.]trigger_name

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

注意事项

创建

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

• start transaction
• commit
• rollback
• set autocommit=0

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

引擎执行的过程

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

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

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

触发器的使用过程

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

例子

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

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