Oracle表审核的添加日期和修改日期 [英] Added date and modified date for Oracle table audit
本文介绍了Oracle表审核的添加日期和修改日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
根据要求,我需要为每个表支持两个必填列:ADDED_DATE,MODIFIED_DATE.
这些旨在用于DBA/审计目的.
According to requirenments I need to support two mandatory columns for each table: ADDED_DATE, MODIFIED_DATE.
These are intended for DBA/auditing purposes.
当我从应用程序内部插入/更新记录时,是否有可能使这些东西完全自动化,并由DB本身隐式支持?
Is it possible to make this stuff totally automatic, implicitly supported by DB itself when I'm inserting / updating records from within application?
推荐答案
在表上创建触发器(在更新每一行之前).
create a trigger on the table (before update for each row).
SQL> create table foo (hi varchar2(10), added_date date, modified_date date);
Table created.
SQL> create trigger foo_auifer
2 before update or insert on foo
3 for each row
4 declare
5 begin
6 if (inserting) then
7 :new.added_date := sysdate;
8 elsif (updating) then
9 :new.modified_date := sysdate;
10 end if;
11 end;
12 /
Trigger created.
SQL> insert into foo (hi) values ('TEST');
1 row created.
SQL> insert into foo (hi) values ('TEST2');
1 row created.
SQL> update foo set hi = 'MODDED' where rownum = 1;
1 row updated.
SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
Session altered.
SQL> select * from foo;
HI ADDED_DATE MODIFIED_DATE
---------- -------------------- --------------------
MODDED 07-nov-2012 15:28:28 07-nov-2012 15:28:39
TEST2 07-nov-2012 15:28:30
SQL>
这篇关于Oracle表审核的添加日期和修改日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文