6.3-数据库触发器.ppt

《6.3-数据库触发器.ppt》由会员分享，可在线阅读，更多相关《6.3-数据库触发器.ppt（20页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、6.4 数据库触发器1 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙了解触发器的基本概念掌握postgreSQL触发器创建、修改、删除的方法理解触发器的执行过程【本节的主要内容】2 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙一、触一、触发发器的基本概念器的基本概念 触发器触发器是特殊类型的存储过程，主要由操作事件是特殊类型的存储过程，主要由操作事件(INSERT、UPDATE、DELETE)触发而被自动执行。触发而被自动执行。触发器可以实现比约束更复杂的数据完整性触发器可以实现比约束更复杂的数据完整性，经常用于经常用于加加强强数据的完数据的完整性整性约约束和束和业务规

2、则业务规则。触发器本身是一个特殊的触发器本身是一个特殊的事事务单务单位位。3 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙触触发发器的特点器的特点u 与表相关联与表相关联：必须定义在表或视图上。：必须定义在表或视图上。u 自动触发自动触发：由执行：由执行INSERT、DELETE、UPDATE操作时触发操作时触发u 不能直接调用，也不能传递或接受参数不能直接调用，也不能传递或接受参数u 是事务的一部分是事务的一部分：触发器和触发语句作为可在触：触发器和触发语句作为可在触 发器内回滚的单个事务。发器内回滚的单个事务。4 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙触触发发

3、器的分器的分类类u 按触发的语句分为：按触发的语句分为：INSERT触发器、触发器、DELETE触发器、触发器、UPDATE触发器触发器 用户动作用户动作 insert delete update应用应用表表insert triggerdelete triggerupdate trigger数据库数据库5 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙触触发发器器的分的分类类（续续）触发器执行的次数可分：触发器执行的次数可分：（1）语句级触发器：）语句级触发器：由关键字由关键字FOR EACH STATEMENT声明，在触发器作用声明，在触发器作用的表上执行一条的表上执行一条SQL语

4、句时，该触发器语句时，该触发器只执行一次只执行一次，即使是修改了零行数据的，即使是修改了零行数据的SQL，也会导致相应的触发器执行。如果都没有被指定，也会导致相应的触发器执行。如果都没有被指定，FOR EACH STATEMENT会会是默认值是默认值。（2）行级触发器：）行级触发器：由关键字由关键字FOR EACH ROW标记的触发器，当触发器作用的标记的触发器，当触发器作用的表的数据发生变化时，表的数据发生变化时，每变化一行就会执行一次触发器每变化一行就会执行一次触发器。例如，假设学生成绩表有。例如，假设学生成绩表有DELETE触发器，当在该表执行触发器，当在该表执行DELETE语句删除记录

5、时，如果删除了语句删除记录时，如果删除了20条记录，条记录，则将导致则将导致 DELETE触发器被执行触发器被执行20 次。次。6 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙触触发发器器的分的分类类（续续）按触发的时间分为三类：按触发的时间分为三类：（1）BEFORE触发器：触发器：在触发事件之前执行触发器。在触发事件之前执行触发器。（2）AFTER触发器：触发器：在触发事件之后执行触发器。在触发事件之后执行触发器。（3）INSTEAD OF触发器触发器：当触发事件发生后，执行触发器中指定的函：当触发事件发生后，执行触发器中指定的函数，而不是执行产生触发事件的数，而不是执行产生触

6、发事件的SQL 语句，从而替代产生触发事件的语句，从而替代产生触发事件的SQL操作。在表或视图上，对于操作。在表或视图上，对于INSERT、UPDATE 或或 DELETE 三种三种触发事件，每种最多可以定义一个触发事件，每种最多可以定义一个INSTEAD OF 触发器触发器7 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙INSTEAD OF 触触发发器器8 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙触触发发器相关的特殊器相关的特殊变变量量（1）NEW 数据类型是数据类型是RECORD。对于行级触发器，它存有。对于行级触发器，它存有INSERT或或UPDATE操作产生

7、的操作产生的新的数据行新的数据行。对于语句级触发器，它的值是。对于语句级触发器，它的值是NULL。（2）OLD 数据类型是数据类型是RECORD。对于行级触发器，它存有。对于行级触发器，它存有被被UPDATE或或DELETE操作修改或删除的操作修改或删除的旧的数据行旧的数据行。对于语句级触发器，它的值是。对于语句级触发器，它的值是NULL。（3）TG_OP 数据类型是数据类型是text；是值为；是值为INSERT、UPDATE、DELETE 的一个字符的一个字符串，它说明触发器是为哪个操作引发。串，它说明触发器是为哪个操作引发。9 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙二二、

8、PostgreSQL创创建触建触发发器的基本器的基本语语法法CREATE TRIGGER 触触发发器名器名 BEFORE|AFTER|INSTEAD OF ON 表名表名 FOR EACH ROW|STATEMENT EXECUTE PROCEDURE 存存储过储过程名程名(参数列表参数列表)（1）指明所定指明所定义义的触的触发发器名器名 （2）BEFORE|AFTER|INSTEAD OF 指明触指明触发发器被触器被触发发的的时间时间（3）ON 表名表名 指明触指明触发发器所依附的表器所依附的表（4）FOR EACH ROW|STATEMENT 指明触指明触发发器被触器被触发发的次数的次数（

9、5）EXECUTE PROCEDURE 存存储过储过程名程名(参数列表参数列表)指明触指明触发时发时所所执执行的存行的存储过储过程程10 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙三三、PostgreSQL创创建触建触发发器的基本步器的基本步骤骤：（1）检查数据库中将要创建的检查数据库中将要创建的触发器所依附的表或视图是否存在触发器所依附的表或视图是否存在，如果不，如果不存在，必须首先创建该表或视图。存在，必须首先创建该表或视图。（2）创建触发器被触发时所要执行的触发器函数创建触发器被触发时所要执行的触发器函数，该函数的类型必须是，该函数的类型必须是TRINGER型，型，是触发器

10、的执行函数是触发器的执行函数。但要注意，有些数据库不需要独立定。但要注意，有些数据库不需要独立定义触发器函数，而是在创建触发器时，定义触发器的过程体。义触发器函数，而是在创建触发器时，定义触发器的过程体。（3）创建触发器创建触发器，一般需要指明触发器依附的表，触发器被触发执行的时，一般需要指明触发器依附的表，触发器被触发执行的时间，触发器是行级触发器还是语句级触发器，触发器执行需要满足的条件。间，触发器是行级触发器还是语句级触发器，触发器执行需要满足的条件。11 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙四四、创创建触建触发发器的示例器的示例假设有stu_score表存储学生的课

11、程成绩，其表结构如下，同时插入如下数据：CREATE TABLE stu_score(sid character(10)NOT NULL,cid character(10)NOT NULL,score numeric(5,1),CONSTRAINT stu_score_pkey PRIMARY KEY(sid,cid)12 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙四四、创创建触建触发发器的例子（器的例子（续续）为了防止非法修改stu_score表的课程成绩，创建audit_score表记录stu_score表的成绩变化，其表结构如下：CREATE TABLE audit_sco

12、re(username character(20),-用户名 sid character(10),cid character(10),updatetime text,-修改的时间 oldscore numeric(5,1),-修改前的成绩 newscore numeric(5,1)-修改后的成绩)13 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙四四、创创建触建触发发器的例子（器的例子（续续）现在创建触发器函数score_audit()14 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙四四、创创建触建触发发器的示例（器的示例（续续）接下来在stu_score表上创建触发

13、器score_audit_triger CREATE TRIGGER score_audit_triger AFTER INSERT OR UPDATE OR DELETE ON stu_score FOR EACH ROW EXECUTE PROCEDURE score_audit();15 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙五、五、验证验证触触发发器的器的执执行行 将课程号为1201的课程成绩增加1分 update stu_score set score=score+1 where cid=1201;stu_score audit_score 16 数据库原理及应用数

14、据库系统原理与开发电子科技大学-陈安龙四四、验证验证触触发发器的器的执执行行(续续)将删除课程号为1501的课程成绩 delete from stu_score where cid=1501;stu_score audit_score 17 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙四四、验证验证触触发发器的器的执执行行(续续)录入学号14503的同学的1201号课程成绩 INSERT INTO stu_score(sid,cid,score)VALUES(14503,1201,83);stu_score audit_score 18 数据库原理及应用数据库系统原理与开发电子科技

15、大学-陈安龙五、触五、触发发器的修改器的修改 ALTER TRIGGER name ON table_name RENAME TO new_name主要参数说明：主要参数说明：（1）name：需要修改的现有触发器的名称。：需要修改的现有触发器的名称。（2）table_name：该触发器作用的表的名字。：该触发器作用的表的名字。（3）new_name：现有触发器的新名字。：现有触发器的新名字。例如：将上述定义的触发器改名为将上述定义的触发器改名为score_audit_trigALTER TRIGGER score_audit_trigger ON stu_score RENAME TO sco

16、re_audit_trig;19 数据库原理及应用数据库系统原理与开发电子科技大学-陈安龙六、触六、触发发器的器的删删除除 DROP TRIGGER IF EXISTS name ON table_name CASCADE|RESTRICT 主要参数说明：主要参数说明：（1）IF EXISTS：如果指定的触发器不存在，那么发出提示而不是抛出错误。：如果指定的触发器不存在，那么发出提示而不是抛出错误。（2）name：要删除的触发器名。：要删除的触发器名。（3）table_name：触发器定义所依附的表的名称。：触发器定义所依附的表的名称。（5）CASCADE：级联删除依赖此触发器的对象。：级联删除依赖此触发器的对象。（6）RESTRICT：如果有依赖对象存在，那么拒绝删除。该参数缺省是拒绝删除。：如果有依赖对象存在，那么拒绝删除。该参数缺省是拒绝删除。例如：将上述触发器例如：将上述触发器score_audit_trig删除，同时级联删除依赖触发器的对象。删除，同时级联删除依赖触发器的对象。DROP TRIGGER IF EXISTS score_audit_trig ON stu_score CASCADE;