触发器
使用触发器的目的
- 维护数据的完整性
- 通过视图改基表
- 审计数据库的操作
实验39：触发器
构建实验表
SQL> drop table d purge;
表已删除。
SQL> drop table e purge;
表已删除。
SQL> create table d as select * from dept;
表已创建。
SQL> create table e as select * from emp;
表已创建。
创建触发器
SQL> create or replace trigger d_update
after delete or update of deptno on d
for each row --行级触发
begin
if (updating and :old.deptno != :new.deptno)
then update e
set deptno =:new.deptno
where deptno=:old.deptno;
end if; --当d表的部门号修改的时候e表的部门号也相应的修改
if deleting then
delete e where deptno=:old.deptno;
end if; --当d表的部门号修改的时候e表的部门号也相应的修改
end;
/
触发器已创建
验证触发器的状态
SQL> select trigger_name,status from user_triggers;
TRIGGER_NAME STATUS
------------------------------ --------
D_UPDATE ENABLED
改变触发器的状态
禁用某个触发器
SQL> alter trigger d_update disable;
触发器已更改
SQL> select trigger_name,status from user_triggers;
TRIGGER_NAME STATUS
------------------------------ --------
D_UPDATE DISABLED
禁用某个表上所有的触发器
SQL> alter table d disable all triggers;
表已更改。
SQL> select * from d;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
删除触发器
SQL> drop trigger d_update;
触发器已删除。
SQL> select trigger_name,status from user_triggers;
未选定行
验证d_update的功能
SQL> update d set deptno=50 where deptno=30;
已更新 1 行。
SQL> select empno,ename from e;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7839 KING
7844 TURNER
7900 JAMES
7902 FORD
7934 MILLER
已选择12行。
SQL> select * from d;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
50 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> delete d where deptno=20;
已删除 1 行。
SQL> select empno,ename,deptno from e;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7499 ALLEN 50
7521 WARD 50
7654 MARTIN 50
7698 BLAKE 50
7782 CLARK 10
7839 KING 10
7844 TURNER 50
7900 JAMES 50
7934 MILLER 10
已选择9行。
SQL> select * from d;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
50 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> commit;
部门编号为20的数据都不存在了。
触发器的类型
行级触发
-
- 行级触发FOR EACH ROW
- 影响的每一行都会执行触发器
语句集触发
-
- 默认的模式，一句话才执行一次触发器
- 触发器不能嵌套，不能含有事物控制语句。
何时触发
before
在条件运行之前，执行触发器
after
在条件运行后，执行触发器
instead of
替代触发，作用在视图上
禁止对表e的sal进行修改
SQL> create or replace trigger e_update
2 before update of sal on e
3 begin
4 if updating then
5 raise_application_error(-20001,'工资不能被改动');
6 end if;
7 end;
8 /
触发器已创建
SQL> select ename,sal from e;
ENAME SAL
---------- ----------
ALLEN 1600
WARD 1250
MARTIN 1250
BLAKE 2850
CLARK 2450
KING 5000
TURNER 1500
JAMES 950
MILLER 1300
已选择9行。
SQL> update e set sal=2000 where ename='ALLEN';
update e set sal=2000 where ename='ALLEN'
*
第 1 行出现错误:
ORA-20001: 工资不能被改动
ORA-06512: 在 "SCOTT.E_UPDATE", line 3
ORA-04088: 触发器 'SCOTT.E_UPDATE' 执行过程中出错
保存老值和新的值
SQL> DROP TABLE T1;
表已删除。
SQL> CREATE TABLE T1 AS SELECT sal old_value,sal new_value from emp where 0=9;
表已创建。
SQL> create or replace trigger trg1
2 before insert or update of sal on emp
3 for each row
4 begin
5 insert into t1 values(:old.sal,:new.sal);
6 end;
7 /
触发器已创建
SQL> select * from t1;
未选定行
SQL> update emp set sal=sal+1;
已更新12行。
SQL> commit;
提交完成。
SQL> select * from t1;
OLD_VALUE NEW_VALUE
---------- ----------
800 801
1600 1601
1250 1251
2975 2976
1250 1251
2850 2851
2450 2451
5000 5001
1500 1501
950 951
3000 3001
1300 1301
已选择12行。
建立一个通过视图来改基表的视图v1
SQL> drop table e1;
表已删除。
SQL> create table e1 as select * from emp;
表已创建。
SQL> drop view v1;
SQL> create view v1 as select distinct deptno from e1;
视图已创建。
试图修改v1时报错
SQL> update v1 set deptno=50 where deptno=10;
update v1 set deptno=50 where deptno=10
*
第 1 行出现错误:
ORA-01732: 此视图的数据操纵操作非法
建立一个替代触发器，当修改v1时会自动修改基表
SQL> create or replace trigger trigger_instead_of
2 instead of insert or update or delete on v1
3 begin
4 if updating then
5 update e1 set deptno=:new.deptno where deptno=:old.deptno;
6 end if;
7 end;
8 /
触发器已创建
SQL> update v1 set deptno=50 where deptno=10;
已更新 1 行。
SQL> select ename,deptno from e1;
ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 50
KING 50
TURNER 30
JAMES 30
FORD 20
ENAME DEPTNO
---------- ----------
MILLER 50
已选择12行。
SQL> select * from v1;
DEPTNO
----------
30
20
50
建立一个登录的审计触发器
SQL> drop table login_table;
SQL> create table login_table(user_id varchar2(15),log_date date,action varchar2(15));
表已创建。
SQL> create or replace trigger logon_trig
2 after logon on schema --on schema方式是只记录当前的用户行为
3 begin
4 insert into login_table(user_id,log_date,action)
5 values(USER,SYSDATE,'Logging on');
6 end;
7 /
触发器已创建
SQL> create or replace trigger logoff_trig
2 before logoff on schema
3 begin
4 insert into login_table(user_id,log_date,action)
5 values(USER,SYSDATE,'Logging off');
6 end;
7 /
触发器已创建
SQL> conn scott/scott
已连接。
SQL> select * from login_table;
USER_ID LOG_DATE ACTION
--------------- -------------- ---------------
SCOTT 17-6月 -13 Logging off
SCOTT 17-6月 -13 Logging on
SQL> conn zhengyu/zhengyu
已连接。
SQL> conn scott/scott
已连接。
SQL> select * from login_table;
USER_ID LOG_DATE ACTION
--------------- -------------- ---------------
SCOTT 17-6月 -13 Logging off
SCOTT 17-6月 -13 Logging on
SCOTT 17-6月 -13 Logging on
SCOTT 17-6月 -13 Logging off
SQL> select user_id,to_char(log_date,'yyyy/mm/dd:hh24:mi:ss') log_date,action from login_table;
USER_ID LOG_DATE ACTION
--------------- ------------------- ---------------
SCOTT 2013/06/17:17:04:55 Logging off
SCOTT 2013/06/17:17:05:42 Logging on
SCOTT 2013/06/17:17:04:56 Logging on
SCOTT 2013/06/17:17:05:33 Logging off
SQL> drop trigger logon_trig;
触发器已删除。
SQL> drop trigger logoff_trig;
触发器已删除。