Oracle-触发器的高级应用

90 阅读 0 评论 60 点赞

我是靠谱客的博主干净网络，这篇文章主要介绍Oracle-触发器的高级应用，现在分享给大家，希望可以做个参考。

1.用触发器实现更新scott.emp工资后,显示员工号,员工工资提升的额度

复制代码

1
2
3
4
5
6
7
8
create or replace trigger t1
after update on scott.emp 
for each row 
  declare 
begin
    dbms_output.put_line(:old.empno||' '||:old.sal||' '||:new.sal);
end t1;

2.创建一个触发器，在修改dept表的部门号后，同时更新emp表中相应的员工的部门号

复制代码

1
2
3
4
5
6
7
8
create or replace trigger t2
after update of deptno on scott.dept
for each row
  declare
begin
    update scott.emp  set deptno=:new.deptno  where deptno=:old.deptno;
end t2;

3.Oracle变异表ORA-04091错误解决方案:

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
针对scott.emp表,为了实现在更新员工所在部门时，该部门中员工人数不超过8人，请建立一个触发器。
以下代码会抛出ORA-04091错误：
create or replace trigger t3
after update on scott.emp 
for each row 
declare
   V_NUM NUMBER;
begin
      SELECT COUNT(*)  INTO V_NUM FROM SCOTT.EMP  WHERE DEPTNO = :NEW.DEPTNO;
      IF V_NUM > 8  THEN
          RAISE_APPLICATION_ERROR(-20001, V_NUM);
     END IF;
end t3;

解决方案：
可以在emp表上创建两个触发器，同时创建一个共享信息的包
一个package，用来共享变量
一个for each row 触发器，用来给共享变量赋值
一个语句级触发器，用来做计算或者统计

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create or replace package pkg
as 
v_deptno scott.dept.deptno%type; 
end pkg;

create or replace trigger t4 
before insert or update of deptno on scott.emp
for each row 
begin
  pkg.v_deptno:=:new.deptno;  
end t4;

create or replace trigger t5
after  insert or update of deptno on scott.emp
declare
  v_num number(3);
begin
  select count(*) into  v_num from scott.emp where deptno=pkg.v_deptno;
  IF v_num>8 THEN
       RAISE_APPLICATION_ERROR(-20003, 'TOO MANY EMPLOYEES IN DEPARTMENT '||pkg.v_deptno);
  END IF;
end t5;

4.创建一个名称为JAME的用户，口令为ANGEL，缺省表空间为USERS，临时表空间为TEMP。Users表空间的定额是10M，登陆数据库前修改口令。

复制代码

1
2
3
4
5
6
7
create user JAME identified by ANGEL
default tablespace users 
temporary tablespace temp
quota 1M on users 
password expire;
grant create session to JAME;