触发器

（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.

转载于:https://www.cnblogs.com/Azaimer/p/11093054.html