Search code examples
sqloracle-databasealtersystimestamp

Alter Column from a date to accept systimestamp

At the current time I have a trigger that updates a date field with sysdate, unfortunately this can sometimes not be unique if an update occurs within the same second. Is it possible to alter a column to use systimestamp so that it using milliseconds and will always be unique.

The triger looks like this:

BEGIN
IF INSERTING or :old.STATUS_FLAG <> :new.STATUS_FLAG THEN
INSERT INTO T_SCHEME_STATUS_HISTORY
  (SCHEME_ID, STATUS_FLAG, DATE_STATUS_CHANGED, AUDIT_CREDENTIALS, AUDIT_DTM)
VALUES
  (:new.SCHEME_ID, :new.STATUS_FLAG, sysdate, :new.AUDIT_CREDENTIALS, SYSDATE);
END IF;  
END;

I want to change the DATE_STATUS CHANGED to use systimestamp so it is always unqiue but the column it is updating is of type DATE(7) so it won't fit.

Is there a way of altering the table to accept systimestamp? Let me know if you need any more information.

Solution

  • @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.