PLSQL触发器课件完整版

1、课程脉络 1 体系结构篇4 安全篇3 PL/SQL语言篇2 对象篇PL/SQL触发器NBA打篮球赛的规则:犯规: 走步、脚踢、 两次带球、打手- 上篮打在你的胳膊或者手上就算打手！但是身体不算！如果他直接把你拉下来是恶意或者技术犯了！实现：存储过程-特殊的存储过程-可以通过存储过程创建规则 -但这种存储过程的执行是系统自动执行的-即只有相应的动作（走步）才执行该存储过程的过程体。该种特殊存储过程叫触发器 引言触发器（trigger）是个特殊的存储过程。特殊性：它的执行不是由程序调用，也不是手工启动，而是由个事件来触发。执行触发器的：比如当对一个表进行操作（ insert，delete， upd

2、ate）时就会激活表自身已经定义好的触发器，即执行触发器。作用：触发器经常用于加强数据的完整性约束和业务规则等，完善系统的性能。触发器优势：提供更精细和更复杂的数据控制能力。例如通过触发器可以实现如下功能：不允许下班后和节假日修改数据库数据。和输出器相关的数据字典：触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。为数据库加上了一层防御和控制功能(“超能力”) 。触发器复习用CHECK短语满足用户的要求例子：创建Student2表，要求Ssex只允许取“男”或“女”。 CREATE TABLE Student (Sno varchar2(9) PRIMARY

3、 KEY， Sname varchar2(8) NOT NULL， Ssex varchar2(2) CHECK (Ssex IN (男，女) ) ， Sage SMALLINT， Sdept varchar2(20) );初识触发器要求：当XS表中记录被删除时，请备份下删除的记录，方式为：写到新建表XS_DEL中,以备查看。需要考虑要素：处理对象：表XS有什么操作时做备份动作：delete何时做：before delete 或者after delete方式：一起还是一条记录一条记录单独做 XS表中被删除的信息做什么：insert into XS_DEL values 触发器头 触发器体 触发

4、器由触发器头部和触发器体两个部分组成，主要包括以下参数：作用对象：表、视图触发事件：DML、DDL、数据库系统事件触发时间：BEFORE、AFTER触发级别：语句级、行级触发条件：WHEN条件触发操作：SQL语句、PL/SQL块触发器组成 CREATE OR REPLACE TRIGGER trigger_name BEFOREAFTERINSTEAD OF DELETE OR INSERTE OR UPDATE OF 列名 ON 表或视图名 FOR EACH ROW WHEN (条件) DECLARE 变量声明部分 BEGIN 执行部分 EXCEPTION 异常处理部分 END 触发器名;触

5、发器语法时间触发事件触发对象触发方式触发事件Insert |delete|update of column在行级触发,为了获得某列在修改前后的数据，-标志符:old和:new触发器示例行级触发和语句级触发器的区别如果定义为语句级，则delete from xs执行时，触发器只运行一次，如果定义为行级，则上面的Delete操作将使触器运行多次（有几条记录就运行几次）。触发器示例行级触发器中的标记符号 :OLD :NEW两种引用方式： :old.field和:new.field （触发器的执行部分） old.field 和new.field (触发器头部的WHEN条件部分)在不同操作中的意义触发语

6、句：old：newINSERT未定义，所有字段都为NULL当语句完成时，将要被插入的值UPDATE更新前行的原始值当语句完成时，将要被更新的值DELETE行被删除前的原始值未定义，所有字段都为NULL触发器【例】功能要求：增加一新表XS_1，表结构和表XS相同，用来存放从XS表中删除的记录。分析:1、创建表 xs_1 create table xs_1 as select * from xs; truncate table xs_1;2、创建一个触发器，当XS表中记录被删除时，请备份下删除的记录，方式为：写到新建表XS_1中,以备查看。2. 触发时间: before3. 触发事件: delet

7、e4. 触发级别: 行级 for each row 触发器示例题目：创建一个触发器，当XS表中记录被删除时，请备份下删除的记录，方式：写到新建表XS_1中,以备查看。create or replace trigger del_xsbefore delete on xs for each rowbegininsert into xs_1 (xh,xm,zym,xb,cssj,zxf) values (:old.xh,:old.xm, :old.zym, :old.xb, :old.cssj,:old.zxf);end del_xs;触发器示例思考：如何触发该触发器？功能需求：监控用户对XS表的操

8、作，要求：当XS表执行插入、更新和删除3种操作后在sql_info表中给出相应提示和执行时间。Create table sql_info(info varchar(10),time date);思考：是否可以放到一个触发器中，如可以则需要判断到底是哪种操作（插入还是更新还是删除）触发器示例2判断当前执行的触发器到底是有那个DML操作激发，使用到三个谓词关键字（仅在触发器中有用）。谓词行为INSERTING如果触发语句是INSERT，则为TRUE；否则为FALSEUPDATING如果触发语句是UPDATE，则为TRUE；否则为FALSEDELETING如果触发语句是DELETE，则为TRUE；否

9、则为FALSE触发器例2：监控用户对XS表的操作，要求：当XS表执行插入、更新和删除3种操作后在sql_info表中给出相应提示和执行时间。Create table sql_info(info varchar(10),time date);触发器示例2create or replace trigger t2 after delete or insert or update on xs for each row declare v_info sql_%type; begin if inserting then v_info:=插入; elsif updating then v_

10、info:=更新; else v_info:=删除; end if; insert INTO SQL_INFO VALUES(v_info,sysdate); end t2;触发器示例2思考如何触发？说明：这两个触发器可以用来加强对用户的行为监督，保障xs表的安全性，并可记录用户对其做过的操作和操作时间。触发器示例2示例3: 为emp表创建一个触发器，当插入新员工时显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员工名。触发器示例3CREATE OR REPLACE TRIGGER t3BEFORE INSERT OR UPDATE OR

11、DELETE ON scott.empFOR EACH ROWBEGIN IF INSERTING THEN DBMS_OUTPUT.PUT_LINE(:new.empno| |:new.ename); ELSIF UPDATING THEN DBMS_OUTPUT.PUT_LINE(:old.sal| |:new.sal); ELSE DBMS_OUTPUT.PUT_LINE(:old.empno| | :old.ename); END IF;END t3;触发触发器t3Set serveroutput ondeclare begin update scott.emp set empno=7

12、521 where empno=7522; commit;end;6.2.1利用SQL语句创建触发器例4：功能要求：针对Scott.emp表，记录其相应操作的信息，具体如下:当执行插入操作时，统计操作后员工人数；当执行更新工资操作时，统计更新后员工平均工资；当执行删除操作时，统计删除后各个部门剩余的人数(游标)。分析：是行级触发，还是 语句级触发？如果定义为语句级，则Deletefromxs执行时，触发器只运行一次，如果定义为行级，则上面的Delete操作将使触器运行多次（有几条记录就运行几次）。本例需要使用语句级触发器触发器示例4CREATE OR REPLACE TRIGGER t4 AF

13、TER INSERT OR UPDATE OR DELETE ON scott.empdeclare v_1 number; v_2 scott.emp.sal%type;begin if inserting then select count(*) into v_1 from scott.emp; DBMS_OUTPUT.PUT_LINE(添加记录后总人数为|v_1); elsif updating then select avg(sal) into v_2 from scott.emp; DBMS_OUTPUT.PUT_LINE(更新记录后平均工资为| |v_2); else for v_

14、s in (select deptno,count(*) num from scott.emp group by deptno) loop DBMS_OUTPUT.PUT_LINE(删除记录后各个部门的部门号和人数为 |v_s.deptno| |v_s.num); end loop; end if; end t4;注意此处不能有for each row触发器4的触发与执行:SQL delete from scott.emp where hiredate select username,activity,to_char(time,yyyy-MM-dd HH24:mi) from u_1;例6 思

15、考：如何通过触发器记录哪个用户，何时退出了系统（即 logoff）？触发器示例5通常用户自定义异常是在声明后才能产生，但raise_application_error函数就可以直接产生异常。一、其定义如下： raise_application_error(错误号,错误信息);错误号和值在-20000到-20999之间，错误信息的文本长度最大不能超过512个字符。二、函数作用: 将应用程序专有的错误从服务器端转达到客户端应用程序，并禁止用户的该项操作。-通过触发器禁止用户的某项操作功能要求：建一触发器，作用为禁止在休息日（周六、周天）改变scott.emp雇员信息（包括添加删除和修改）。crea

16、te or replace trigger tr_sec_empbefore insert or update or delete on scott.empbegin if to_char(sysdate, DY) in (星期六,星期日) then raise_application_error(-20001,不能在休息日修改员工信息); end if;end;通过触发器禁止用户的某项操作SQL update scott.emp set ename=candy where empno=7876; update scott.emp set ename=candy where empno=787

17、6 ORA-20001: 禁止在周六周日修改员工数据ORA-06512: 在 SYSTEM.T01, line 6ORA-04088: 触发器 SYSTEM.T01 执行过程中出错 触发该触发器触发器的组成:new 和:old的应用 (for reach row)Inserting updating deleting的应用系统触发器举例两个有用的函数To_char(sysdate,DY)RAISE_APPLICATION_ERROR(错误号,错误信息);触发器小结启动和禁止触发器在某些情况下可能需要临时禁止触发器,比如它引用的数据库对象已经无效,或者需要执行大量的数据操作,此时不希望触发器工作

18、,或者避免造成延时等.Alter trigger 触发器名字 disable;Alter trigger 触发器名字 enable;关闭某表的所有触发器 alter table 表名字 disable all triggers ; -开启所有触发器 alter table 表名字 enable all triggers ;休息一会习题用触发器实现 更新scott.emp工资后,显示员工号,员工工资提升了额度create or replace trigger t101 after update on scott.emp for each row declare begin dbms_output.put_line(:old.empno| |:old.sal| |:new.sal); end t101;创建一个触发器，在修改dept表的部门号后，同时更新emp表中相应的员工的部门号。分析:触发事件 update of deptno 表 scott.dept 触发时间 aftercreate or replace trigger tr_up