Menu
Is there a way to add column DATE_CREATED such as only new rows will pickup the default sysdate? When I ran the ALTER below, all priors rows got the DATE_CREATED set to the run-time of the ALTER script; I would prefer them to remain null.
alter table abc.mytable
add
(DATE_CREATED date default sysdate null
);
300
This Oracle ALTER TABLE example will add a column called customer_name to the customers table that is a data type of varchar2(45). In a more complicated example, you could use the ALTER TABLE statement to add a new column that also has a default value: ALTER TABLE customers ADD city varchar2(40) DEFAULT 'Seattle';
You can create a After insert or update trigger on that table, which will insert sysdate in that column whenever a row is updated. Mutating problem problem will occur if you create before update trigger. To solve this , you need to create after update trigger. this will help .
where the <format> string has the same options as in TO_CHAR. Finally, you can change the default DATE format of Oracle from "DD-MON-YY" to something you like by issuing the following command in sqlplus: alter session set NLS_DATE_FORMAT='<my_format>'; The change is only valid for the current sqlplus session.
If a new column is added to a table, the column is initially NULL unless you specify the DEFAULT clause. When you specify a default value, the database immediately updates each row with the default value.
You need to first add the column without a default:
alter table mytable add date_created date default null;
and then add define the default value:
alter table mytable modify date_created default sysdate;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
