通过oracle触发器记录修改某表的用户和ip地址

通过oracle触发器记录修改某表的用户和ip地址

不过需要提前建立好触发器才可以记录下

先建立好这样的表，用来存放用户名、ip、时间。

SQL> desc modifyemp;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 IP                                                 VARCHAR2(20)
 USERNAME                                           VARCHAR2(20)
 CURRTIME                                           DATE

SQL> create or replace trigger modifyemp
  2  before insert or update on emp
  3  for each row
  4  declare
  5  ip varchar2(20);
  6  user varchar2(20);
  7  currtime date;
  8  begin
  9  select sysdate into currtime from dual;
 10  select sys_context('userenv','ip_address'),sys_context('userenv','SESSION_USER') into ip,user from dual;
 11  insert into modifyemp values(ip,user,currtime);
 12  end emp;
 13  /

触发器已创建

 

SQL> select * from modifyemp;

未选定行

SQL> select * from emp;

     EMPNO ENAME      JOB                        MGR HIREDATE              SAL 
---------- ---------- ------------------- ---------- -------------- ---------- 
      COMM     DEPTNO TEST                                                     
---------- ---------- ---------------------------------                        
      7782 CLARK      MANAGER                   7839 09-6月 -81           8000 
                   10                                                          
                                                                               
      7839 KING       PRESIDENT                      17-11月-81           5000 
                   10                                                          
                                                                               

SQL> update emp set sal=1000 where empno=7782;

已更新 1 行。

SQL> commit;

提交完成。

SQL> select * from modifyemp;

IP                   USERNAME             CURRTIME                             
-------------------- -------------------- --------------                       
                     MZL                  26-7月 -08                           

 

SQL> spool off;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-410239/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12778571/viewspace-410239/