（1）创建记录表记录对表的操作
create table operation_log(
id number(10) primary key,
username varchar2(15),
time date,
operate varchar2(10),
tablename varchar2(10),
empno varchar2(10),
oldsal number(4),
newsal number(4));
（2）创建一个主键序列用于对表的主键列赋值
create sequence operationid increment by 1 start with 1 maxvalue 9999999 nocycle nocache;
（3）创建存储过程，代码如下：
create or replace trigger tri_operation
before
insert or delete or update of sal
on emp
for each row
begin
if updating then
insert into operation_log values(operationid.nextval,user,sysdate,'update','scott.emp',:old.empno,:old.sal,:new.sal);
elsif inserting then
insert into operation_log values(operationid.nextval,user,sysdate,'insert','scott.emp',:new.empno,null,:new.sal);
else
insert into operation_log values(operationid.nextval,user,sysdate,'delete','scott.emp',:old.empno,:old.sal,null);
end if;
end;
（4）进行表操作使得验证触发器是否其作用
insert into emp(empno,sal) values(1234,5555);
update emp set sal=9999 where empno=1234;
delete from emp where empno=1234;
（5）查看表，看对表的操作是否成功记录
select * from operation_log;
2 使用INSTEAD OF 触发器将对视图的操作转换为对基表的操作
conn scott/tiger
create view v_test(empno,ename,dname) as select e.empno,e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
insert into v_test(empno,ename,dname) values('1111','test','testdept');
insert into v_test(empno,ename,dname) values('1111','test','testdept')
*
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view
create or replace trigger tri_v_test
instead of insert on v_test
for each row
declare
temptag number(3);
begin
select count(*) into temptag from emp where empno=:new.empno;
if temptag=0 then
insert into emp(empno,ename) values(:new.empno,:new.ename);
end if;
end;
/
Trigger created.
insert into v_test(empno,ename,dname) values('1111','test','testdept');
1 row created.
直接对视图v_test插入数据是非法的。但是通过建立instead of触发器可以实现向emp表插入数据，请思考如何向dept表也插入数据。视图的结构和触发器都需要进行修改才可以实现。
3 使用DDL触发器阻止对表的删除
（1）设计模式级创建触发器阻止对emp表的删除
create or replace trigger tri_emp
before drop on schema
begin
if ora_dict_obj_name='EMP' then
raise_application_error(-20003,'drop talbe is not allowed');
end if;
end;
/
（2）删除emp表验证触发器
drop table emp;
drop table emp
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20003: drop talbe is not allowed
ORA-06512: at line 3
（3）设计数据库级创建触发器阻止对emp表的删除
conn / as sysdba
Connected.
show user
USER is "SYS"
create or replace trigger tri_emp
before drop on database
begin
if ora_dict_obj_name='EMP' then
raise_application_error(-20003,'drop talbe is not allowed');
end if;
end;
/
Trigger created.
（4）删除emp表验证触发器
drop table scott.emp;
drop table scott.emp
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20003: drop talbe is not allowed
ORA-06512: at line 3
4 使用系统触发器记录用户登录和退出数据库的情况
（1）创建数据库启动触发器
create table tri_db_event(time date,event varchar2(10));
create or replace trigger tri_startup
after startup on database
begin
insert into tri_db_event values(sysdate,’STARTUP’);
end;
（2）创建数据库关闭触发器
create or replace trigger tri_shutdown
before shutdown on database
begin
insert into tri_db_event values(sysdate,’SHUTDOWN’);
end;
（3）关闭和启动数据库，验证触发器
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 238530560 bytes
Fixed Size 1335724 bytes
Variable Size 150998612 bytes
Database Buffers 79691776 bytes
Redo Buffers 6504448 bytes
Database mounted.
Database opened.
SQL> select * from tri_db_event;
TIME EVENT
--------- ----------
07-NOV-18 shutdown
07-NOV-18 startup
SQL> select to_char(time,'yyyy-mm-dd hh24:mi:ss') as operate_time,event from tri_db_event;
OPERATE_TIME EVENT
------------------- ----------
2018-11-07 16:24:53 shutdown
2018-11-07 16:26:04 startup
（4）创建用户登录和退出系统触发器
create table tri_user_log(username varchar2(20),time_logon date,time_logoff date);
create or replace trigger tri_user_logon
after logon on database
begin
insert into tri_user_log(username,time_logon)values(user,sysdate);
end;
create or replace trigger tri_user_logoff
before logoff on database
begin
insert into tri_user_log(username,time_logoff)values(user,sysdate);
end;
（5）使用不同用户登录验证触发器
conn scott/tiger
conn / as sysdba
conn scott/tiger
conn / as sysdba
SQL> select username, to_char(time_logon,'yyyy-mm-dd hh24:mi:ss') as logon_time, to_char(time_logoff,'yyyy-mm-dd hh24:mi:ss') as logoff_time from tri_user_log;
USERNAME LOGON_TIME LOGOFF_TIME
-------------------- ------------------- - ------------------
SYS 2018-11-07 16:37:20
SYS 2018-11-07 16:38:21
SYS 2018-11-07 16:38:21
SYS 2018-11-07 16:38:28
SCOTT 2018-11-07 16:38:28
SCOTT 2018-11-07 16:38:35
SYS 2018-11-07 16:38:35
SYS 2018-11-07 16:38:40
SCOTT 2018-11-07 16:38:41
SCOTT 2018-11-07 16:38:45
SYS 2018-11-07 16:38:45
11 rows selected.