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月-15SCOTT                          26-10月-15UPDATE

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）使用同样的语句测试触发器。