Oracle自动添加当前日期 [英] Oracle auto add current date

问题描述

我想创建一个表'product'并有一个列date，当我向表中添加一些信息时，是否有可能添加当前日期?

I want create a table 'product' and have a column date, is it possible that current date will be added when I add some info to table?

如果是，请提供此表的示例

If yes please example of this table

create table products (
id number not null,
date number not null
);

推荐答案

假设

1. 您的列实际上未命名为date，因为这是保留字
2. 您的列实际上定义为date而不是number
3. 您要在插入新行时填充列

1. Your column is not actually named date since that is a reserved word
2. Your column is actually defined as a date rather than as a number
3. You want to populate the column when you insert a new row

您可以为列定义默认值.

you can define a default value for the column.

SQL> ed
Wrote file afiedt.buf

  1  create table products (
  2  id number not null,
  3  dt date   default sysdate not null
  4* )
SQL> /

Table created.

SQL>
SQL> insert into products( id ) values( 1 );

1 row created.

SQL> select * from products;

        ID DT
---------- ---------
         1 20-NOV-12

如果要在UPDATE行时修改dt列，则需要一个触发器

If you want to modify the dt column when you UPDATE the row, you would need a trigger

CREATE OR REPLACE TRIGGER trg_products
  BEFORE INSERT OR UPDATE ON products
  FOR EACH ROW
BEGIN
  :new.dt := sysdate;
END;

触发器将覆盖作为dt列的INSERT或UPDATE语句的一部分传递的任何值.默认值不会.

A trigger will override any value passed in as part of the INSERT or UPDATE statement for the dt column. A default value will not.

这篇关于Oracle自动添加当前日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！