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

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

[sql] view plain copy

  1. create table EMP(
  2.     EMPNO NUMBER,
  3.     ENAME VARCHAR2(10),
  4.     JOB VARCHAR2(9),
  5.     MGR NUMBER,
  6.     HIREDATE DATE,
  7.     SAL BINARY_DOUBLE,
  8.     COMM BINARY_DOUBLE,
  9.     DEPTNO NUMBER
  10. );

其中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

部门表

[sql] view plain copy

  1. create table dept(
  2.     DEPTNO NUMBER,
  3.     DNAME VARCHAR2(50)
  4. );

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伪记录
变量,识别值的状态。

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

(4)触发器可用于
–数据确认
–实施复杂的安全性检查
–做审计,跟踪表上所做的数据操作等
–数据的备份和同步

举个例子,当我向员工表中插入一条记录的时候,我们就可以在插入后显示“插入成功!”字符。
这个就是使用触发器在插入语句后自动prompt一个提示语句。
还有,在主数据库进行增删改的时候,可以使用触发器,在从数据库中也进行相应的增删改。

(5)查询触发器、过程及函数
select * from user_triggers;
select * from user_source;

(6)触发器实例
①最简单的触发器实例:
插入一条记录后,自动打印“成功插入一条员工”

[sql] view plain copy

  1. create or replace trigger firstTrigger –创建一个触发器
  2. after insert
  3. on emp
  4. begin
  5.   dbms_output.put_line(‘成功插入一条员工’);
  6. end;
  7. /

我们插入一条数据:

②限制非工作时间向数据库插入数据
这里我们使用的是“语句级”的触发器。

非工作时间:
周末、上班前和下班后。

要用到的sql语句
select to_char(sysdate,’day’) from dual; –查询今天是星期几
select to_number(to_char(sysdate,’hh24′)) from dual; –查询现在是几点

语句:

[sql] view plain copy

  1. create or replace trigger securityEmp
  2. before insert
  3. on emp
  4. begin
  5.   if to_char(sysdate,‘day’in (‘星期六’,‘星期日’,‘星期一’or
  6.   or to_number(to_char(sysdate,‘hh24’)) not between 9 and 18 then
  7.   raise_application_error(-20001,‘不能在非工作时间插入数据!’);
  8. end;
  9. /

效果:

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

语句:

[sql] view plain copy

  1. create or replace trigger checkSal
  2. before update of sal
  3. on emp
  4. for each row
  5. begin
  6.   if :new.sal<:old.sal then
  7.     raise_application_error(-20001,‘涨后的薪水不能少于涨前的薪水’);
  8.   end if;
  9. end;
  10. /

效果:

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

[sql] view plain copy

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

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

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

未经允许不得转载:JX BLOG » 【oracle学习】9.触发器以及应用场景

赞 (0)

评论 0

评论前必须登录!

登陆 注册