【oracle学习】9.触发器以及应用场景

前言
先介绍一下我们下面要用到的两张表以及数据：
员工信息表

create table EMP(
    EMPNO NUMBER,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER,
    HIREDATE DATE,
    SAL BINARY_DOUBLE,
    COMM BINARY_DOUBLE,
    DEPTNO NUMBER
);

其中job是职位，mgr是该员工的上司的id，sal是工资，comm是提成，deptno是所属部门。

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------------- -------------------- ------------------ --------------- -------------- ----- ----- ---------------

1110 张三主管 1110 12-3月 -14 5200 0 20

1111 李四销售 1116 03-11月-15 3400 500 30

1112 王五销售 1116 25-4月 -12 4400 800 30

1113 赵二后勤 1110 30-5月 -11 3450 0 40

1114 李磊磊会计 1110 22-12月-15 2500 0 50

1115 张少丽销售 1110 11-3月 -16 2400 1400 30

1116 林建国主管 1116 22-1月 -16 5700 0 20

1117 马富邦后勤 1116 22-7月 -13 2800 0 40

1118 沈倩会计 1116 06-5月 -10 2100 0 50

部门表

create table dept(
    DEPTNO NUMBER,
    DNAME VARCHAR2(50)
);

SQL> select * from dept t;

DEPTNO DNAME

-------- --------

20 管理部门

30 销售部门

40 后勤部门

50 金融部门

(1)触发器

数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定

的数据库操作语句(insert，update，delete)在指定的表上发出时，

Oracle自动地执行触发器中定义的语句序列。

(2)创建触发器语句

create [or replace] trigger 触发器名

{before|after}

{delete|insert|update[of 列名]}

on 表名

[for each row[when(条件)]]

plsql块

(3)触发器的类型

A.语句级触发器

在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。

B.行级触发器(有FOR EACH ROW的)

触发语句作用的每一条记录都被触发。在行级触发器中使用:old和:new伪记录

变量，识别值的状态。

【oracle学习】9.触发器以及应用场景

其实就是一个针对表，一个针对行。

(4)触发器可用于

--数据确认

--实施复杂的安全性检查

--做审计，跟踪表上所做的数据操作等

--数据的备份和同步

举个例子，当我向员工表中插入一条记录的时候，我们就可以在插入后显示“插入成功!”字符。

这个就是使用触发器在插入语句后自动prompt一个提示语句。

还有，在主数据库进行增删改的时候，可以使用触发器，在从数据库中也进行相应的增删改。

(5)查询触发器、过程及函数

select * from user_triggers;

select * from user_source;

(6)触发器实例

①最简单的触发器实例:

插入一条记录后，自动打印“成功插入一条员工”

create or replace trigger firstTrigger --创建一个触发器
after insert
on emp
begin
  dbms_output.put_line('成功插入一条员工');
end;
/

【oracle学习】9.触发器以及应用场景

我们插入一条数据:

【oracle学习】9.触发器以及应用场景

②限制非工作时间向数据库插入数据

这里我们使用的是“语句级”的触发器。

非工作时间:

周末、上班前和下班后。

要用到的sql语句

select to_char(sysdate,'day') from dual; --查询今天是星期几

select to_number(to_char(sysdate,'hh24')) from dual; --查询现在是几点

语句:

create or replace trigger securityEmp
before insert
on emp
begin
  if to_char(sysdate,'day') in ('星期六','星期日','星期一') or
  or to_number(to_char(sysdate,'hh24')) not between 9 and 18 then
  raise_application_error(-20001,'不能在非工作时间插入数据！');
end;
/

效果:

【oracle学习】9.触发器以及应用场景

③确认数据

确认员工信息表emp中，工资sal的修改值不能低于原值

语句:

create or replace trigger checkSal
before update of sal
on emp
for each row
begin
  if :new.sal<:old.sal then
    raise_application_error(-20001,'涨后的薪水不能少于涨前的薪水');
  end if;
end;
/

效果:

【oracle学习】9.触发器以及应用场景

④限制

限制每个部门只招聘5名员工，超过计划则报出错误信息

create or replace trigger checkLimit
before insert
on emp
for each row
declare
  num number :=0;
begin
  select count(*) into num from emp where deptno=:new.deptno;
  if num+1 > 5 then
    raise_application_error(-20001,'每个部门最多只能招聘5名员工');
  end if;
end;
/

测试，我们原来就有2条部门20的数据，我们在添加四条:

【oracle学习】9.触发器以及应用场景

可以看到，在添加第四条的时候报错了。

秒客网

【oracle学习】9.触发器以及应用场景

相关文章