Oracle表审核的添加日期和修改日期

本文介绍了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>