-------------------------写一个触发器：所有的dml操作都输出操作信息---------- create or replace trigger trigger6 before insert or delete or update on student for each row begin if inserting then dbms_output.put_line('进行的是插入操作插入的信息如下：姓名:' ||:new.name ||'年龄:' ||:new.age ); elsif deleting then dbms_output.put_line ('进行的是删除操作删除的信息如下：姓名:' ||:old.name ||'年龄:' ||:old.age ); elsif updating then dbms_output.put_line ('进行的是更新操作原信息如下：姓名:' ||:new.name ||'年龄:' ||:new.age ); dbms_output.put_line ('新信息如下姓名:' ||:old.name ||'年龄:' ||:old.age ); else dbms_output.put_line ('做的是其他操作' ); end if; end;
| --------------写一个模式触发器---------------------------- create table dropped_obj (obj_name varchar2( 20), obj_type varchar2(20),drop_date date); create or replace trigger trigger1 before alter or drop on schema --针对于该用户对象的删除操作进行触发 begin insert into dropped_obj values(ora_dict_obj_name,ora_dict_obj_type,sysdate ); end ; SQL> drop table student; Table dropped SQL> select * from dropped_obj;--会发现刚刚删除的操作被记录下来 OBJ_NAME OBJ_TYPE DROP_DATE -------------------- -------------------- ----------- STUDENT TABLE 19- 1月 -15 6 : SQL> --使用的变量： --Ora_client_ip_address 返回客户端的ip地址 --Ora_database_name 返回当前数据库名 --Ora_login_user 返回登录用户名 --Ora_dict_obj_name 返回ddl操作所对应的数据库对象名 --Ora_dict_obj_type 返回ddl操作所对应的数据库对象的类型 --------------写一个数据库触发器---------------------------- create table event_table ( event varchar2( 30), time date ); create or replace trigger trigger_start after startup on database begin insert into event_table values (ora_sysevent ,sysdate ); end ; create or replace trigger trigger_shut before shutdown on database; begin insert into event_table values (ora_sysevent ,sysdate ); end ; SQL> shutdown immediate; --关闭数据库事件 Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started.--开启数据库事件 Total System Global Area 400846848 bytes Fixed Size 2213776 bytes Variable Size 251660400 bytes Database Buffers 142606336 bytes Redo Buffers 4366336 bytes Database mounted. Database opened. SQL> select * from event_table;--两个事件都被记录下来了 EVENT TIME ------------------------------ ------------ SHUTDOWN 19- JAN- 15 STARTUP 19- JAN- 15 --------------写一个登录触发器触发器---------------------------- create table log_table (username varchar2( 20), logon_time date , logoff_time date, address varchar2( 20)); create or replace trigger trigger_login after logon on database begin insert into log_table values(Ora_login_user,sysdate ,null ,Ora_client_ip_address ); end ; create or replace trigger trigger_logout before logoff on database begin update log_table lt set lt.logoff_time = sysdate where lt.username = Ora_login_user; end; SQL> exit;--退出操作 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning , OLAP , Data Mining and Real Application Testing options [oracle@ localhost ~]$ sqlplus / as sysdba --登录操作 SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 19 06: 37: 49 2015 Copyright ( c) 1982 , 2009, Oracle. All rights reserved. Connected to : Oracle Database 11 g Enterprise Edition Release 11.2 .0.1.0 - 64 bit Production With the Partitioning , OLAP , Data Mining and Real Application Testing options SQL> select * from log_table; USERNAME LOGO |