第7章_触发器.ppt

1、1,第7章 触发器,2,7.1.1 触发器的概念与作用,触发器类似于存储过程和存储函数，都是有声明、执行和异常处理过程的PL/SQL有名块。 当触发事件发生时，由系统自动地隐式调用并执行触发器，而不能由应用程序显式地调用执行。 触发器不接受任何参数。,3,7.1.1 触发器的概念与作用,触发器主要作用是： 维护那些通过创建表时的声明约束不可能实现的复杂的完整性约束以及对数据库中特定事件进行监控和响应。 利用触发器记录所进行的修改以及谁做了修改等信息对表进行审计。 当表被修改的时候，触动触发器自动给需要执行操作的程序发信号。,4,7.1.2 触发器的类型,DML触发器 替代触发器 系统触发器,5

2、,7.1.3 触发器的组成,6,7.1.4 触发器与存储过程的区别,7,7.2 DML触发器,建立在基本表上的触发器称为DML触发器。当对基本表进行数据的INSERT、UPDATE和DELETE操作时，会激发相应的DML触发器的执行。,8,7.2.1 DML触发器执行顺序,执行BEFORE语句级触发器。 对于受语句影响的每一行： 执行BEFORE行级触发器； 执行DML语句； 执行AFTER行级触发器。 执行AFTER语句级触发器。,9,7.2.2 语句级触发器,通过CREATE TRIGGER语句创建一个语句级触发器，该触发器在一个数据操作语句发生时只触发一次。,10,7.2.2 语句级触发

3、器,语句级触发器创建的语法 CREATE OR REPLACE TRIGGER trigger_name BEFORE|AFTER trigger_event1 OR trigger_event2 OF column_name ON table_name WHEN trigger_condition PL/SQL block,11,7.2.2 语句级触发器,【例7-1】创建一个语句级的INSERT触发器。 CREATE OR REPLACE TRIGGER tg_insert AFTER INSERT ON departments BEGIN DBMS_OUTPUT.PUT_LINE (您执行

4、了插入操作); END tg_insert; SQL INSERT INTO departments VALUES(220,edu,103,2500);,12,7.2.2 语句级触发器,【例7-2】创建一个 BEFORE型语句级触发器。禁止周六、周日对employees表进行DML操作，如果在周六、周日对employees表进行了任何操作，则中断操作，并提示用户不允许在此时间对employees表进行操作。 CREATE OR REPLACE TRIGGER trigger_emp BEFORE INSERT OR UPDATE OR DELETE ON employees BEGIN IF

5、TO_CHAR(sysdate,DY) IN (SAT,SUN)THEN RAISE_APPLICATION_ERROR (-20500,Can”t operate in weekend); END IF;END trigger_emp; SQL UPDATE employees SET salary=1000;,13,7.2.2 语句级触发器,使用触发器谓词 如果触发器响应多个DML事件，而且需要根据事件的不同进行不同的操作，则可以在触发器体中使用谓词判断是哪个触发事件触动了触发器。,14,7.2.2 语句级触发器,【例7-3】为employees表创建一个 AFTER型语句级触发器。当执行

6、插入操作时，统计操作后员工人数；当执行更新工资操作时，统计更新后员工平均工资；当执行删除操作时，统计删除后最大员工编号。 见书90页【例7-3】,15,7.2.2 语句级触发器,练习：为employees表创建一个语句级触发器，当执行更新雇员工资操作时，统计更新后所有雇员的最高工资、最低工资、平均工资，并输出。,16,7.2.3 行级触发器,通过 CREATE TRIGGER语句中指定FOR EACH ROW子句创建一个行级触发器，一个DML操作涉及多少行记录，触发器就被执行多少次。,17,7.2.3 行级触发器,行级触发器创建的语法 CREATE OR REPLACE TRIGGER tri

7、gger_name BEFORE|AFTER trigger_event1 OR trigger_event2 OF column_name ON table_name FOR EACH ROW WHEN trigger_condition PL/SQL block,18,7.2.3 行级触发器,【例7-4】创建一个行级的DELETE触发器。 CREATE OR REPLACE TRIGGER tg_delete AFTER DELETE ON departments FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE (您执行了删除操作); END tg_dele

8、te; SQL DELETE FROM departments;,19,7.2.3 行级触发器,使用行级触发器标识符 在行级触发器中，在列名前加上:old标识符表示该列变化前的值，在列名前加上:new标识符表示该列变化后的值。,20,7.2.3 行级触发器,【例7-5】创建一个触发器，在修改deptartments表的部门号时，同时更新employees表中相应的员工的部门号。 CREATE OR REPLACE TRIGGER tg_update_dept AFTER UPDATE OF department_id ON departments FOR EACH ROW BEGIN UPDA

9、TE employees SET department_id=:new.department_id WHERE department_id=:old.department_id; END tg_update_dept; SQL UPDATE departments SET department_id=30 WHERE department_id=50;,21,7.2.3 行级触发器,行级触发器使用WHEN子句 保证当行级触发器被触发时只有在当前行满足一定限制条件时，才执行触发器体的PL/SQL语句。 WHEN子句后面是一个逻辑表达，当逻辑表达式的值为TRUE时，执行触发器体。如果逻辑表达式为F

10、ALSE，不执行触发器体。,22,7.2.3 行级触发器,【例7-6】创建一个带限制条件的UPDATE触发器，修改雇员的工资时，只输出80号部门雇员修改前工资的值与改后的工资的值。 见书94页【例7-6】,23,7.2.3 行级触发器,【例7-7】创建一个行级触发器，当向departments表中插入数据时，将插入后的值写入到deptlog日志表中，当删除departments数据时，将被删除前的值写入到日志表中，当对departments表中某一列进行更新时，将更新前和更新后的值写入到日志表中。 见书94页【例7-7】,24,7.2.3 行级触发器,练习：使用WHEN子句创建一个行级触发器，

11、修改员工工资时，保证修改后的工资高于修改前的工资，否则请利用RAISE_APPLICATION_ERROR过程抛出错误号“-20001”的错误，错误信息为“工资太低!”。,25,7.3 INSTEAD OF触发器,如果视图是从多个基本表使用连接操作导出的，则不允许修改。 如果视图在创建过程中使用聚组函数（SUM，AVG，MAX，MIN，COUNT），则不允许修改。 如果视图在创建过程中使用DISTINCT操作符，则不允许修改。 如果视图在创建过程中使用GROUP BY子句，则不允许修改。 如果视图在创建过程中使用集合操作符（UNION，MINUS，INTERSECT），则不允许修改。 如果视图

12、在创建过程中带有WITH READ ONLY选项，则不允许修改。,7.3.1 视图修改的限制,26,7.3.1 视图修改的限制,【例7-8】创建一个基于departments和employees两个表连接的视图dept_emp，并对该视图进行插入操作。 见书96页【例7-8】,27,7.3.2 INSTEAD OF触发器的创建,INSTEAD OF触发器的主要作用是修改一个本来不可以被修改的视图。 INSTEAD OF触发器是建立在视图上的触发器，响应视图上的DML操作。 INSTEAD OF触发器必须是行级触发器。,28,7.3.2 INSTEAD OF触发器的创建,INSTEAD OF触发

13、器创建的语法： CREATE OR REPLACE TRIGGER trigger_name INSTEAD OF trigger_event1 OR trigger_event2 OF column_name ON view_name FOR EACH ROW WHEN trigger_condition PL/SQL block,29,7.3.2 INSTEAD OF触发器的创建,【例7-9】在视图dept_emp上创建一个INSTEAD OF触发器，解决上例中不能对视图进行DML操作的问题。 见书97页【例7-9】,30,7.4 系统触发器,Oracle提供的系统触发器是建立在数据库或模

14、式上的触发器，可以在DDL事件或数据库事件上被触发。,7.4.1 系统触发器触发事件,31,7.4.2 系统触发器的创建,系统触发器创建的语法： CREATE OR REPLACE TRIGGER trigger_name BEFORE|AFTER trigger_event1 OR trigger_event2 OF column_name ON DATABASE|SCHEMA WHEN trigger_condition PL/SQL block,32,7.4.2 系统触发器的创建,【例7-10】将每个用户的登录信息写入到temp_table表中。 CREATE OR REPLACE TRIGGER log_user_connection AFTER LOGON ON DATABASE BEGIN INSERT INTO edu.temp_table VALUES (user,sysdate); END log_user_connection;,33,7.5 触发器的管理,修改触发器 CREATE OR REPLACE TRIGGER 禁用触发器 可以禁用某个触发器 ALTER TRIGGER trigger_name DISABLE; 可以禁用某个表对象上的所有触发器 ALTER TRIGGER table_name DIS