Automatically populate date in oracle table

问题:

I have created a table in oracle XE, and I have a field with type date. I would like if possible when I insert a row, that it automatically fills that field with the current date from the system.

I am inserting the rows from the SQL prompt.

Thanks

回答1:

Here is how, you need to format your table properly:

create table test (first number
                   , second timestamp default systimestamp
                   , third varchar2(12));

And your default value is always current system time formatted as timestamp.

回答2:

change the field after creating the table

ALTER TABLE table MODIFY time_collumn TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

回答3:

Or you could also use a trigger:

CREATE OR REPLACE TRIGGER date_trigger

BEFORE INSERT

ON table_name

REFERENCING NEW AS NEW

FOR EACH ROW

BEGIN

SELECT sysdate INTO :NEW.column_name FROM dual;

END;

回答4:

The below snippet might be helpful if we forget to add the constraint while creating the table:

ALTER TABLE TABLE_NAME 
ADD CONSTRAINT CONSTRAINT_NAME
COLUMN_NAME DATA_TYPE DEFAULT CURRENT_DATE;