SQL Oracle 列是否有自动修改时间戳类型？

Is there an automatic modification time stamp type for Oracle columns?

提问by Ayrad

Is there a way to create a timestamp column in Oracle that automatically stores a timestamp of when the record has changed ?

有没有办法在 Oracle 中创建一个时间戳列来自动存储记录更改时的时间戳？

采纳答案by OMG Ponies

Tables I've modelled always include:

我建模的表总是包括：

• CREATED_USER, VARCHAR2
• CREATED_DATE, DATE
• UPDATED_USER, VARCHAR2
• UPDATED_DATE, DATE

• CREATED_USER, VARCHAR2
• CREATED_DATE,日期
• UPDATED_USER, VARCHAR2
• UPDATED_DATE,日期

...columns. Why implement a trigger when you can set the value at the same time as the INSERT/UPDATE?

...列。当您可以在 INSERT/UPDATE 的同时设置值时，为什么要实现触发器？

INSERT INTO TABLE (...CREATED_DATE, UPDATED_DATE) VALUES (...,SYSDATE, SYSDATE);

UPDATE TABLE
   SET ...,
       UPDATED_DATE = SYSDATE

回答by dkackman

Pretty sure you have to do this with a trigger in Oracle:

很确定您必须使用 Oracle 中的触发器执行此操作：

create or replace TRIGGER parkedorder_tbiur
   BEFORE INSERT OR UPDATE
   ON parkedorder
   REFERENCING OLD AS old_row NEW AS new_row
   FOR EACH ROW
BEGIN
   IF INSERTING
   THEN
      IF :new_row.ID IS NULL
      THEN
         SELECT parkedorder_seq.NEXTVAL
           INTO :new_row.ID
           FROM DUAL;
      END IF;
   END IF;

   IF    :new_row.lastupdated <> SYSDATE
      OR :new_row.lastupdated IS NULL
   THEN
      SELECT sysdate
        INTO :new_row.lastupdated
        FROM DUAL;
   END IF;

   SELECT SYS_CONTEXT ( 'USERENV', 'OS_USER' )
     INTO :new_row.lastupdatedby
     FROM DUAL;
END;

回答by David Oneill

Yes, via a trigger:

是的，通过触发器：

create or replace
TRIGGER schema.name_of_trigger
BEFORE INSERT OR UPDATE ON schema.name_of_table
FOR EACH ROW
BEGIN
    :new.modified_on := SYSTIMESTAMP;
END;

This assumes your table has a field called modified_on.

这假设您的表有一个名为 modified_on 的字段。

As has been noted above, a trigger is an ideal candidate anytime you have multiple different places where the table gets updated. If you only have one function/procedure that can update the table, just do it there, and skip the trigger.

如上所述，只要您有多个不同的表更新位置，触发器都是理想的候选者。如果您只有一个可以更新表的函数/过程，只需在那里执行，并跳过触发器。

回答by devpl

For oracle I usually use a trigger to update the timestamp field

对于 oracle 我通常使用触发器来更新时间戳字段

CREATE OR REPLACE TRIGGER update_timestamp 
  BEFORE INSERT OR UPDATE ON some_table
  FOR EACH ROW
BEGIN
  :NEW.TS := systimestamp;
END;

Oracle does not seem to have a built-in attribute for updating the timestamp field to the current timestamp (unlike other DBs like MySQL).

Oracle 似乎没有用于将时间戳字段更新为当前时间戳的内置属性（与 MySQL 等其他数据库不同）。

回答by David Aldridge

You can get very close to this by querying ORA_ROWSCN: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns007.htm#sthref825

您可以通过查询非常接近此ORA_ROWSCN：http: //download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns007.htm#sthref825

This is more accurate if you created the table with the ROWDEPENDENCIES option.

如果您使用 ROWDEPENDENCIES 选项创建表，这会更准确。

It actually logs the commit time for the record ...

它实际上记录了记录的提交时间......

drop table tester 
/

create table tester (col1 number, col2 timestamp)
rowdependencies
/

insert into tester values (1, systimestamp)
/

(approximate five second pause)

commit
/

select t.ora_rowscn,
       SCN_TO_TIMESTAMP(t.ora_rowscn),
       t.col1,
       t.col2
from   tester t
/

ORA_ROWSCN             SCN_TO_TIMESTAMP(T.ORA_ROWSCN) COL1                   COL2
---------------------- ------------------------------ ---------------------- -------------------------
9104916600628          2009-10-26 09.26.38.000000000  1                      2009-10-26 09.26.35.109848000 

回答by Jim Hudson

Another way to deal with this is by turning on fine-grained audit. The individual rows won't have a timestamp, but you'll have a record of all changes. Overkill in most situations, though -- I usually just use triggers.

解决此问题的另一种方法是启用细粒度审计。各个行没有时间戳，但您将拥有所有更改的记录。不过，在大多数情况下，我通常只使用触发器。

If you are OK with nearest .01 seconds, you can use date format and assign sysdate. If you need more detail, use the timestamp.

如果您对最近的 0.01 秒没问题，您可以使用日期格式并指定 sysdate。如果您需要更多详细信息，请使用时间戳。