Is There an Automatic Modification Time Stamp Type for Oracle Columns

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

Tables I've modelled always include:

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

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

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

UPDATE TABLE
   SET ...,
       UPDATED_DATE = SYSDATE

Auto update timestamp column in oracle and sql server, without triggers

For SQL Server, take a look at rowversion.

However, this will only give you an "arbitrary" versioning of your row, and will not provide an actual time stamp of your updates:

The rowversion data type is just an incrementing number and does not preserve a date or a time.

As far as I know, a true time-stamp version in SQL Server is not possible without a trigger.

On insert or update i want to update UPDATED_AT column with current timestamp

There is no data type of datetime in Oracle. There is a date which always has the day and the time to the second. Or timestamp which includes fractional seconds. Or timestamp with [local] time zone that includes a time zone. Since you are using current_timestamp, I'll guess that you intended the data type to be timestamp with time zone.

There is also no on update property for a column. If you want the column to be updated whenever the row is updated, you'd need to write a trigger for that.

ALTER TABLE EMPLOYEE
ADD (
  UPDATED_AT TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

create trigger set_updated_at
  before update on employee
  for each row
begin
  :new.updated_at := current_timestamp();
end;

Alter Column from a date to accept systimestamp

@Boneist's remarks about not using time values as keys are spot on. HOWEVER, if you're absolutely bound and determined to do this - yeah, sure, it can be done:

ALTER TABLE WHATEVER
  MODIFY (SOME_DATE_FIELD TIMESTAMP(6));

This changes the data type to a TIMESTAMP(6), which is accurate down to 1/1,000,000 of a second. Hopefully this will satisfy your requirement, but really - date/time fields should never be used as a unique key.

Best of luck.

Formatting timestamp in oracle

This is a pretty terrible data model. A timestamp should really, really be stored in a timestamp column rather than in a varchar2. Both because then the data type identifies what is actually in the column and because it is more efficient and because it lets you use all Oracle's timestamp functions on the data sensibly.

Assuming that you are stuck with an incorrect data model

update your_table
   set your_column = to_char( to_timestamp( your_column, 'YYYY-MM-DD:HH24:MI:SS.FF9' ),
                              'MM/DD/YYYY HH:MI:SS.FF3 PM' )

would update all the data to the new format in the unlikely event that every single value in the table is in the correct format already. In most real-world systems, you'd need to do a fair amount of clean-up first because inevitably someone has stored in incorrect string or two in your column.

If you do happen to be able to update all the data successfully, be aware that any queries that do order by your_column will almost certainly stop doing what you want. Since the column is a varchar2 rather than a timestamp, sorting is done alphabetically rather than by the point in time that the string represents. If you change the format to something where temporal order doesn't match alphabetical order, you are likely to have unhappy users.

Automatically populate date in oracle table

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.

Oracle row change time stamp

Oracle uses System Change Number (SCN). You can access it in a query as ora_rowscn. However, by default it represents the time the entire block was modified not a particular row. If you want row level information, your table must be created with the rowdependencies specifier.

And you are making incorrect assumptions about how materialized views work. Oracle keeps track of changes using tables MLOG$ and $RUPDS.

---

Related Topics