oracle课堂收录-触发器

1.语句级触发器

先举个栗子：

（1）建立一个日志信息表emp_log用于存储用户对表的操作。

SQL> create table emp_log(
  2     who varchar2(30),
  3     when date);

表已创建。

（2）在emp表上创建语句级触发器，将用户对emp表进行的操作记录到emp_log表中。

SQL> create or replace trigger emp_op
  2     before insert or update or delete
  3     on emp
  4  begin
  5     insert into emp_log(who,when)
  6     values(user,sysdate);
  7  end emp_op;
  8  /

触发器已创建

（3）更新emp表，增加员工10%的薪金，确认触发器正常运行。

SQL> update emp
  2  set sal=sal*1.1;

已更新17行。

SQL> select * from emp_log;

WHO                            WHEN
------------------------------ --------------
SCOTT                          26-10月-15

修改触发器emp_op和emp_log表以便能够记录操作的类型。

（1）修改emp_log表，围棋添加action列。

SQL> alter table emp_log
  2  add (action varchar2(50));

表已更改。

SQL> desc emp_log
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -------------

 WHO                                                VARCHAR2(30)
 WHEN                                               DATE
 ACTION                                             VARCHAR2(50)

（2）修改触发器，以便记录操作的类型

SQL> create or replace trigger emp_op
  2     before insert or update or delete
  3     on emp
  4  declare
  5     var_action varchar2(50);
  6  begin
  7     if inserting then
  8             var_action:='INSERT';
  9     elsif updating then
 10             var_action:='UPDATE';
 11     elsif deleting then
 12             var_action:='DELETE';
 13     end if;
 14     insert into emp_log(who,when,action)
 15     values(user,sysdate,var_action);
 16  end emp_op;
 17  /

触发器已创建

（3）更新某雇员的信息，测试触发器。

SQL> update emp
  2  set sal=sal*1.2
  3  where empno=7369;

已更新 1 行。

SQL> select * from emp_log;

WHO                            WHEN
------------------------------ -------------
ACTION
--------------------------------------------
SCOTT                          26-10月-15


SCOTT                          26-10月-15
UPDATE

2.行级触发器

举个栗子：

（1）创建一个测试表foo以及随同使用的序列，序列的作用是生成一组排序数。

SQL> create table foo(sid number,sname varchar2(20));

表已创建。

SQL> create sequence seq_foo;

序列已创建。

（2）创建生成主键的行级触发器。

SQL> create or replace trigger foo_trigger
  2  before insert or update of sid
  3  on foo
  4  for each row
  5  begin
  6     if inserting then
  7             select seq_foo.nextval
  8             into :new.sid
  9             from dual;
 10     else
 11             raise_application_error(-20020,'不允许更新ID值!');
 12     end if;
 13  end;
 14  /

触发器已创建

（3）向foo表中插入两条数据，测试触发器能否正常运行。

SQL> insert into foo(sid,sname)
  2  values(1,'董鹏');

已创建 1 行。

SQL> insert into foo(sid,sname)
  2  values(1,'刘丽');

已创建 1 行。

SQL> select * from foo;

       SID SNAME
---------- --------------------
         1 董鹏
         2 刘丽

以上可以看出，无论是否提供sid值，sid都会使用seq_foo.nextval的值。

3.instead of触发器

前提这个触发器是定义在视图上的。举个栗子吗？

（1）创建一个视图，显示雇员的基本信息和所在部门的名称。

SQL> connect sys/oracle as sysdba
已连接。
SQL> grant create view to scott;

授权成功。

SQL> connect scott/sunshumin
已连接。
SQL> create view emp_dep_view
  2  as select empno,ename,job,sal,dname
  3  from emp,dept
  4  where emp.deptno=dept.deptno;

视图已创建。

（2）如果视图向表中添加记录，则由于视图引用了两个表，添加记录失败。

SQL> insert into emp_dep_view(empno,ename,job,sal,dname)
  2  values(8000,'董鹏','MANAGER',1500,'SALES');
insert into emp_dep_view(empno,ename,job,sal,dname)
                                             *
第 1 行出现错误:
ORA-01776: 无法通过联接视图修改多个基表

（3）为视图创建一个instead of触发器。

SQL> create or replace trigger insert_emp_deb_trigger
  2     instead of
  3     insert on emp_dep_view
  4     for each row
  5  declare
  6     var_deptno emp.deptno%type;
  7  begin
  8     insert into emp(empno,ename,job,sal)
  9     values(:new.empno,:new.ename,:new.job,:new.sal);
 10
 11     select deptno
 12     into var_deptno
 13     from dept
 14     where dname=:new.dname;
 15
 16     update emp
 17     set deptno=var_deptno
 18     where empno=:new.empno;
 19  end insert_emp_deb_trigger;
 20  /

触发器已创建

（4）使用同样的语句测试触发器。