6.3 数据库触发器在线视频

同学们大家好

我是主讲教师陈安龙

下面我们开始学习数据库编程的第4小节

这一节我们将要学习的主要内容

1）了解触发器的基本概念

2）掌握postgreSQL触发器创建、修改、删除的方法

3）理解触发器的执行过程

一 触发器的基本概念

触发器是特殊类型的存储过程

主要由操作事件(INSERT、UPDATE、DELETE)

触发而被自动执行

触发器可以实现比约束更复杂的数据完整性

经常用于加强数据的完整性约束和业务规则

触发器本身是一个特殊的事务单位

当触发器中的语句执行过程

由于出现异常而终止时

数据库管理系统会回滚触发器对数据库的改变

触发器的特点

与表相关联 必须定义在表或视图上

自动触发：由执行INSERT DELETE UPDATE操作时触发

不能直接调用 也不能传递或接受参数

是事务的一部分：触发器和触发语句作为

可在触发器内回滚的单个事务

触发器的分类

按触发的语句分为 INSERT触发器 DELETE触发器 UPDATE触发器

在数据库表上执行delete语句将会触发delete触发器

在数据库表上执行update语句将会触发update触发器

触发器的分类

触发器执行的次数可分

（1）语句级触发器：由关键字FOR EACH STATEMENT声明

在触发器作用的表上执行一条SQL语句时

该触发器只执行一次

即使是修改了零行数据的SQL

也会导致相应的触发器执行

如果都没有被指定

FOR EACH STATEMENT会是默认值

（2）行级触发器：由关键字FOR EACH ROW标记的触发器

当触发器作用的表的数据发生变化时

每变化一行就会执行一次触发器

例如 假设学生成绩表有DELETE触发器

当在该表执行DELETE语句删除记录时

如果删除了20条记录

则将导致 DELETE触发器被执行20 次

按触发的时间分为三类：

（1）BEFORE触发器：在触发事件之前执行触发器

（2）AFTER触发器：在触发事件之后执行触发器

（3）INSTEAD OF触发器：当触发事件发生后

执行触发器中指定的函数

而不是执行产生触发事件的SQL 语句

从而替代产生触发事件的SQL操作

在表或视图上

对于INSERT、UPDATE 或 DELETE 三种触发事件

每种最多可以定义一个INSTEAD OF 触发器

INSTEAD OF 触发器

由于视图是来自于基表数据的虚表

大多数视图不支持对视图的insert delete update操纵

应用程序在视图上执行insert delete update时

可以使用INSTEAD OF 触发器会代替执行

对基表的insert delete update操纵

触发器相关的特殊变量

（1）NEW 数据类型是RECORD

对于行级触发器

它存有INSERT或UPDATE操作产生的新的数据行

对于语句级触发器 它的值是NULL

（2）OLD 数据类型是RECORD

对于行级触发器

它存有被UPDATE或DELETE操作修改或删除的旧的数据行

对于语句级触发器 它的值是NULL

（3）TG_OP数据类型是text

值为INSERT、UPDATE、DELETE 的一个字符串

它说明触发器是为哪个操作引发

有的数据库系统把new和old看作一张只读的表

可以使用select查询语句进行查询 如ORACLE

二、PostgreSQL创建触发器的基本语法

CREATE TRIGGER 加触发器名

后面跟上相应的触发器参数

（1）指明所定义的触发器名

（2）BEFORE | AFTER | INSTEAD OF

指明触发器被触发的时间

（3）ON 表名 指明触发器所依附的表

（4）FOR EACH

指明触发器被触发的次数

（5）EXECUTE PROCEDURE 存储过程名 ( 参数列表 )

指明触发时所执行的存储过程

三、PostgreSQL创建触发器的基本步骤

（1）检查数据库中将要创建的触发器所依附的表

或视图是否存在

如果不存在 必须首先创建该表或视图

（2）创建触发器被触发时所要执行的触发器函数

该函数的类型必须是TRINGER型

是触发器的执行函数 但要注意

有些数据库不需要独立定义触发器函数

而是在创建触发器时

定义触发器的过程体

（3）创建触发器 一般需要指明触发器依附的表

触发器被触发执行的时间

触发器是行级触发器还是语句级触发器

触发器执行需要满足的条件

注意：postgreSQL创建触发器的方式不同于其它数据库

如ORACLE

下面我们来介绍创建触发器的示例

假设有stu_score表存储学生的课程成绩

其表结构如下

有sid、cid、score

同时插入对应的数据在成绩表中

为了防止非法修改stu_score表的课程成绩

可创建audit_score表记录成绩表的成绩变化

其表结构如下：

主要包括用户名 修改时间 修改前的成绩 修改之后的成绩

现在创建触发器函数

如果是执行的delete语句

则将删除的记录从old中读出写入审计表

如果是执行的update语句

则将修改前的记录从old中读出 修改后的记录从new读出写入审计表

如果是执行的insert语句

则将插入的记录从new中读出写入审计表；

接下来在stu_score表上创建触发器

主要定义该触发器同时支持INSERT OR UPDATE OR DELETE

并且是行触发器

五 验证触发器的执行

将课程号为1201的课程成绩增加1分

update stu_score set score=score+1 where cid='1201'

修改之后的成绩表的数据

audit_score是存储的修改前的学生和修改后的学生成绩

下面将删除课程号为1501的课程成绩

使用 delete from 语句

stu_score删除之后的成绩

audit_score是在触发器触发之后的数据

录入学号14503的同学的1201号课程成绩

INSERT INTO 语句

录入成绩之后的stu_score

当触发器触发之后

审计表audit_score的记录

五、触发器的修改

ALTER TRIGGER name ON table_name RENAME TO new_name

主要参数说明

name：需要修改的现有触发器的名称

table_name：该触发器作用的表的名字

new_name：是触发器的新名字

例如：将上述定义的触发器改名为score_audit_trig

六 触发器的删除

当我们不需要触发器的时候

使用DROP TRIGGER 删除触发器

主要参数说明：

（1）IF EXISTS：如果指定的触发器不存在 那么发出提示

（2）name：要删除的触发器名

（3）table_name：触发器定义所依附的表的名称

（5）CASCADE：级联删除依赖此触发器的对象

（6）RESTRICT：如果有依赖对象存在

那么拒绝删除

该参数缺省是拒绝删除

例如：将上述触发器score_audit_trig删除

同时级联删除依赖触发器的对象