Oracle触发器

触发器

触发器是什么

触发器是在指定一些特定的sql语句(必须是增删改语句)时自动执行的一些pl/sql的代码片段。注意，查询一般不会

加触发器。触发器也是命名块，但是和之前学习的过程函数等命名块不同，触发器会自动执行。

触发器的特点：

1，触发器满足条件会自动执行

2，触发器只能加上增删改语句操作上，不能加到查询语句上。

3，一个表上最多只能加12个触发器

4，触发器不是加的越多越好，一个表最多不要超过6个触发器。触发器会影响查询效率

5，触发器文本的大小不能超过36kb，如果太大，可以把触发器的封装到过程或者函数中。

6，触发器中不能有ddl（数据定义命令，好比创建表，修改表结构，删除表等）操作，也不能有事物操作。

触发器的作用：

1，可以记录用户的日志记录

2，可以预防一些非法的操作，好比可以预防用户在周六或者周日操作某些表

触发器在oracle中有三种分类，dml触发器 替代触发器 系统触发器

dml触发器

dml 是数据操作语句，包括数据库表的增删改操作。

语法：

create [or replace] trigger 触发器名称

before/after 触发时机 before 是数据操作之前 after 数据操作之后

delete/update/insert [of 列名1,列名2...] 触发操作 多个触发操作可以用 or 连接，后面还可以跟上数据表的列 of

列名

on

表名称 触发对象

for each row 表示该触发器是行级触发器还是语句级触发器。行级触发器针对每个表的行都会执行一遍触发器，语

句级触发器只会执行一次。如果去掉for each row就是语句级触发器.

when 条件表达式 触发条件

begin

语句块；

end;

 

举例：当删除部门信息时，把用户删除掉的部门存到历史表中

--创建表，让表结构参考某一个已经存在的表，如果不希望复制表数据，可以
--在sql查询后加一个永远不成立的条件
drop table dept_his;
create table dept_his as select * from dept where 1=0;
create or replace trigger tri_dept_his
--触发时机
after
-- 触发操作
delete
on
--触发对象
dept
--是行级触发器
for each row
begin
--往历史表中插入用户删除的数据,获取用户删除的数据可以用old这个内置变量
--引用内置变量的时候要在内置变量前加冒号
insert into dept_his values(:old.deptno,:old.dname,:old.loc);
end;
--测试
select * from dept;
delete from dept where deptno=4;
select * from dept_his;

 

举例：写一个触发器，不让用户周六周日操作（增删改）部门表数据

create or replace trigger tri_dept_oper
before insert or update or delete on dept
begin
--判断当前日期是否是周六或者周日
if to_char(sysdate,'day') in ('星期六','星期日') then
--阻止用户操作 raise_application_error第一个参数是异常编号
-- 这里可用的异常编号是-20999到-20000之间
raise_application_error(-20000,'非工作日不能修改部门表数据');
end if;
end;
select * from dept;
update dept set dname='住建部1' where deptno=4;

触发器的执行顺序

一个表上有可能有行级触发器，有可能有语句级触发器：

before 的语句级触发器

befroe的行级触发器

dml语句

after的行级触发器

after的语句级触发器

 

举例：部门编号为10的员工的基本工资不能降低，部门编号为20的员工信息不能删除

create or replace trigger tri_emp_oper
after update or delete on emp
for each row
--注意 在when中内置变量old就不需要加冒号了
when (old.deptno=10 or old.deptno=20)
begin
--部门编号为10的员工的基本工资不能降低
if :old.deptno=10 and :new.sal<:old.sal and updating then
raise_application_error(-20001,'部门编号为10的员工的基本工资不能降低');
end if;
--部门编号为20的员工信息不能删除
--此处需要判断用户对部门编号为20的员工执行的是什么操作
-- 需要用到触发器谓词 deleting updating inserting
if :old.deptno=20 and deleting then
raise_application_error(-20002,'部门编号为20的员工的信息不能删除');
end if;
end;
--修改部门编号为10的基本工资
select * from emp;
update emp set sal='2000' where empno=7782;
--删除部门编号为2员工的信息
delete from emp where empno=7566;

触发器中调用存储过程

举例：删除员工表数据时，把删除的数据存到历史表中

先创建历史表：

create table emp_his as select * from emp where 1=0;

创建触发器:

--创建一个过程，存储用户历史数据
create or replace procedure pro_emp_his (
empno varchar2,ename varchar2
) as
begin
insert into emp_his(empno,ename) values(empno,ename);
end;
--创建触发器 在触发器中调用过程
create or replace trigger tri_emp_his
after delete on emp for each row
begin
--调用过程
pro_emp_his(:old.empno,:old.ename);
end;