Oracle-触发器编程及OEM中PL/SQL编程

触发器编程及OEM中PL/SQL编程

实践目的

　　(1) 掌握触发器数据库对象的基本作用。

　　(2) 掌握触发器的建立、修改、查看、删除操作。

实践内容

　　下列任务中涉及的数据表是SCOTT用户给出的表。

　　(1) 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

(2) 为emp表创建一个触发器，当插入新员工时显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员工名。 

(3) 修改员工工资时，保证修改后的工资高于修改前的工资。

(4) 将每个用户的登录信息（用户名，登陆时间）写入temp_table表中。

(5)创建一个包括员工及其所在部门信息的视图empdept，然后向视图中插入一条记录（2345,’TOM’,3000,’SALES’）

实验步骤：

　下列任务中涉及的数据表是SCOTT用户给出的表。

(1) 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

先创建删除日志表log_emp

SQL> create table log_emp as select * from emp;

 

表已创建。

 

SQL> delete from log_emp;

 

已删除14行。

 

先将emp表进行个复制，也就是建立一个emp_backup。将emp表的整个数据及表结构都复制到emp_backup中，避免删除数据时为以后的操作造成不必要的麻烦。

SQL> create table emp_backup as select * from emp;

 

表已创建。

 

 

SQL> select * from emp_backup;

 

再创建触发器

SQL> create or replace trigger tr_del_emp

  2  before delete

  3  on emp_backup

  4  for each row

  5  begin

  6  Insert into log_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)

  7  values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);

  8  end;

  9  /

 

触发器已创建

SQL>

 

 

 

删除empno=7369对应的这行数据

 

SQL> delete from emp_backup where empno='7369';

 

已删除 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from log_emp;

 

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM

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

    DEPTNO

----------

      7369 SMITH      CLERK           7902 17-12月-80            800

        20

SQL>

 

 

 

 

（2）为emp表创建一个触发器，当插入新员工时显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员工名。 

SQL> create or replace trigger emp_oper_tri

  2  before delete or update or insert on emp_backup

  3  for each row

  4  begin

  5      case

  6     when inserting then

  7     dbms_output.put_line('empno:'||:new.empno||' '||'ename:'||:new.ename);

  8     when updating then

  9     dbms_output.put_line('before:'||:old.sal||' '||'(after)sal:'||:new.sal);

 10     when deleting then

 11     dbms_output.put_line('empno:'||:old.empno||' '||'ename'||:old.ename);

 12     end case;

 13

 14  end;

 15  /

 

触发器已创建

 

SQL> insert into emp_backup(empno,ename)

  2   values('8888','xiaoli');

empno:8888 ename:xiaoli

 

已创建 1 行。

 

SQL>  delete from emp_backup where empno='8888';

empno:8888 enamexiaoli

 

已删除 1 行。

SQL>

SQL>  update emp_backup

  2   set sal='6666'

  3   where empno='7654';

before:9500 (after)sal:6666

 

已更新 1 行。

 

 

 

（3）修改员工工资时，保证修改后的工资高于修改前的工资。

SQL> create or replace trigger ch_trigger_sal

  2  before update of sal on emp_backup

  3  for each row

  4  begin

  5     if :new.sal<=:old.sal then

  6     raise_application_error('-20001','The salary is lower');

  7     end if;

  8  end;

  9  /

 

触发器已创建

 

SQL> update emp_backup

  2  set sal=9000

  3  where empno='7654';

 

已更新 1 行。

 

 

 

这时候emp_backup表中的员工号为7654的工资由原来的1250变成了9000.

更改员工号为7654的工资改为小于9000，再来看创建的触发器成功没。发现当更改员工号为7654的工资改为小于9000时，则触发器创建成功。

SQL> update emp_backup

  2  set sal=1500

  3  where empno='7654';

update emp_backup

       *

第 1 行出现错误:

ORA-20001: The salary is lower

ORA-06512: 在 "SCOTT.CH_TRIGGER_SAL", line 3

ORA-04088: 触发器 'SCOTT.CH_TRIGGER_SAL' 执行过程中出错

 

 

当更改员工号为7654的工资改为大于9000时，再看触发器创建成功没，

发现当，则触发器创建成功。如下图：

SQL> update emp_backup

  2  set sal=9500

  3  where empno='7654';

已更新 1 行。

 

在查看emp_backup表：

 

SQL> select * from emp_backup;

 

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO

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

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30

      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30

      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20

      7654 MARTIN     SALESMAN        7698 28-9月 -81           9500       1400         30

      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30

      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10

      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20

      7839 KING       PRESIDENT            17-11月-81           5000                    10

      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30

      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20

      7900 JAMES      CLERK           7698 03-12月-81            950                    30

 

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO

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

      7902 FORD       ANALYST         7566 03-12月-81           3000                    20

      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

 

已选择13行。

 

（4）将每个用户的登录信息（用户名，登陆时间）写入temp_table表中。

先创建temp_table表：

SQL> create table temp_table(user_name varchar(15),user_time timestamp)

 

表已创建。

再创建一个temp_table_trigger触发器用以实现将每个用户的登录信息（用户名，登陆时间）写入temp_table表中：

SQL> create or replace trigger temp_table_trigger

  2  after logon on schema

  3  begin

  4  insert into temp_table values(user,sysdate);

  5  end;

  6  /

 

触发器已创建

 

SQL> conn scott/tiger;

已连接。

 

SQL> select * from scott.temp_table;

 

USER_NAME       USER_TIME

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

SCOTT           24-11月-14 12.05.51.000000 下午

 

 

 

 

 

 

(5)创建一个包括员工及其所在部门信息的视图empdept，然后向视图中插入一条记录（2345,’TOM’,3000,’SALES’）

 

SQL>  create or replace view empdept

  2   as

  3   select empno,ename,sal,dname

  4   from emp,dept

  5   where emp.deptno=dept.deptno;

 

视图已创建。

 

SQL>  insert into empdept values('2345','tom','3000','sales');

 insert into empdept values('2345','tom','3000','sales')

*

第 1 行出现错误:

ORA-01776: 无法通过联接视图修改多个基表

 

 

SQL>  CREATE OR REPLACE  TRIGGER  trig_view

  2   INSTEAD OF INSERT ON empdept

  3   FOR  EACH ROW

  4   DECLARE

  5      v_deptno  dept.deptno%type;

  6   BEGIN

  7      select deptno  into v_deptno from dept  where dname=:new.dname;

  8      insert into emp(empno,ename,sal,deptno)  values(:new.empno,:new.ename,v_deptno,:new.sal);

  9   END  trig_view;

 10  /

 

触发器已创建

 

 

 

 

 

实验小结：

1. 根据触发器作用的对象的不同，触发器分为三类：（1）DML触发器：建立在基本表上的触发器，响应基本表的Insert，Update，Delete操作。（2）Instead Of触发器：建立在视图上的触发器，响应视图上的Insert，Update，Delete操作。（3）系统触发器：建立在系统或模式上的触发器，响应系统事件和DDL（Create，Alter，Drop）操作。

2. 建立在基本表上的触发器成为DML触发器。当对基本表进行的数据的Insert、Update和Delete操作时，会激发相应的DML触发器的执行。

3. 触发器是一种特殊类型的存储过程，编译后存储在数据库服务器中，当特定事件发生时，由系统自动调用和执行，而不能由应用程序现实的调用执行。此外，触发器不接受任何参数。

4. 触发器由触发器头部和触发器体两个部分组成，主要包括以下参数。（1）作用对象：表、视图、数据库和模式。（2）触发事件：激发触发器执行的事件。如DML、DDL、数据库系统事件等，可以将多个事件用关系运算符OR组合。（3）触发时间：用于指定触发器在触发事件完成之前还是完成之后。After/Before.（4）触发级别：触发级别用于指定触发器响应触发事件的方式。默认为语句级触发器，即触发事件发生后，触发器只执行一次。如果指定为For Each Now，即为行级触发器。（5）触发条件：由When子句指定一个逻辑表达式，当触发事件发生，而且When条件为True时，触发器会执行。（6）触发操作：触发器执行所进行的操作。

5. 触发器主要作用于维护那些通过创建表时的声明约束不可能实现的复杂的完整性约束以及对数据库中特定事件进行操控和响应。