目录
触发器
(一)概念
触发器是在特定事件出现的时候,自动执行的代码块。
触发器是自动隐式运行
触发器不能有参数
(二)触发前提:在建立触发器之前,要对以下内容进行分析
1. 触发事件:DML操作、DDL操作和系统事件等
2. 触发时间:before after
3. 触发对象:表、视图、用户和数据库
4. 触发级别:
语句级触发(表级触发):无论操作影响了多少行,语句触发器只被调用一次
行级触发器:操作影响到的每一行,都会执行触发器。
5. 触发限制:一个逻辑表达式,当该表达式为真时,触发器被触发执行
(三)创建
语法:
- Create or replace trigger <触发器名>
- [before|after]
- <触发事件> on <表名>
- [for each row]
- [when <条件表达式>]
<PL/SQL程序体>
(四)DML 触发器
根据触发器的四个应用场景确定的案例
1. 实施复杂的安全性检查:
例:禁止在非工作时间操作emp表
知识点:三个谓词:instering deleting updating
create or replace
trigger emptime2
before insert or delete or update on emp
declare
v_riqi varchar2(10);
v_hh number(2);
begin
v_riqi:=to_char(sysdate,'day');
v_hh:=to_number(to_char(sysdate,'hh24'));
if(v_riqi in('星期六','星期日') or (v_hh>17 or v_hh<8)) THEN
if inserting then
raise_application_error(-20013,'非工作时间禁止插入');
elsif deleting then
raise_application_error(-20014,'非工作时间禁止删除');
elsif updating then
raise_application_error(-20015,'非工作时间禁止更新');
else
raise_application_error(-20016,'非工作时间禁止任何操作');
end if;
end if;
end;
验证:在emp表上做增删改操作,都会触发。
2. 数据完整性检查
例:修改后的工资不能比原工资低
知识点:两个伪记录变量 :old :new
这两个变量只在触发器内部有效,并且只有在DML触发表中字段时才有效。使用形式::old.字段名和:new.字段名
:old和 :new 表示的是同一条记录。两个都是记录变量
:old 是这条记录被操作之前;:new 是这条记录被操作之后。
create or replace trigger checksal
before update
on emp
for each row
begin
if :old.sal>:new.sal then
dbms_output.put_line('涨前:'||:old.sal||',涨后:'||:new.sal);
RAISE_application_error(-20002,'涨后的工资不能小于涨前的工资');
end if;
end;
验证:修改某员工的工资,使其工资值减少。
3. 数据库的审计
例:当工资超过6000元时,查看该员工的信息
先要创建表:Create table audit_info(info varchar(200));
再创建触发器:
create or replace trigger shji_info
after update on emp
for each row
begin
if :new.sal>6000 then
insert into audit_info values(:new.empno||' '||:new.ename||' '||:new.sal);
end if;
end;
验证:修改员工的工资为大于6000
4. 数据的备份和同步
例:员工涨工资后,会同步备份到备份表中
先建立备份表:create table emp_back as select * from emp;
再建立触发器:
create or replace trigger copysal
after update on emp
for each row
begin
update emp_back set sal=:new.sal where empno=:new.empno;
end;
验证:修改emp表中某员工的工资,再查看备份表中的数据
(五)替代触发器
解决多表视图的更新问题,替代视图完成对多个表中数据的修改。
替代触发器都是行级触发。因为触发操作不会执行,所以没有before和after
例:当向视图中插入新记录时,被认定为非法。试用触发器完成插入操作
create or replace trigger view_insert_tigger
instead of insert on v_emp20
for each row
declare
v_empCount NUMBER;
v_deptCount NUMBER;
begin
--判断要增加的员工是否存在
SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:NEW.empno;
--判断要部门是否存在
SELECT COUNT(deptno) INTO v_deptCount FROM dept WHERE deptno=:new.deptno;
--如果部门不存在
IF v_deptCount=0 THEN
INSERT INTO dept(deptno,dname,loc)VALUES(:new.deptno,:new.dname,:new.loc);
END IF;
--如果员工不存在
IF v_empCount=0 THEN
INSERT INTO emp(empno,ename,job,sal,deptno)
VALUES(:new.empno,:new.ename,:new.job,:new.sal,:new.deptno);
END IF;
end ;
(六)DDL触发器
例:记录用户所有的DDL操作
先建立日志表:
create table ddl_log(lname varchar2(30),obj_type varchar2(20),eventname rchar2(20),ddl_date date);
再创建触发器
create or replace trigger trig_ddl
after ddl on schema
begin
insert into ddl_log values(ora_dict_obj_name,ora_dict_obj_type,sysdate,ora_sysevent);
end;