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
Add a comment
|
4 Answers
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.
4 Comments
APC
If the column has datatype of
timestamp why not default to systimestamp rather than sysdate?
ant
what is the difference? I use sysdate, if you can explain that would be great for me as well for Andan who asked the question
Jeffrey Kemp
date (which is what
sysdate returns) is only accurate to the nearest second, timestamp (returned by systimestamp) has greater precision (e.g. 1000'th of a second or better)
Miad Abdi
@ant I need updated_date and created_date to be automatically populated. like
timestamps property in mongoose. I need created_date to be populated when record is created and never change. and I want update_date to be set to the same value as created_date when record is first created. and then automatically update it when the record update (created_date should not update). How can I achieve that in Oracle xe?change the field after creating the table
ALTER TABLE table MODIFY time_collumn TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
answered Jan 16, 2013 at 9:28
Zhasulan Berdibekov
1,0883 gold badges20 silver badges39 bronze badges
Comments
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;
1 Comment
bugybunny
stackoverflow.com/a/10300790/4040068 says that Oracle does not implement DEFAULTs as CONSTRAINTS. I’m on a really old Oracle version (11g) but a quick Google search also didn’t help in finding out if this is a new feature. Are you sure your
ADD CONSTRAINT is for Oracle and not another DBMS? Because in my case I get the error " ORA-01430: column being added already exists in table 01430. 00000 - "column being added already exists in table"". Oracle tries to add a new column and not only a constraint.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;
1 Comment
Monarch Wadia
This works better if you need to ensure that the timestamp is never entered manually. Although that case may be rare.