Re: SQL Server rowversion or timestamp equivalent in Oracle 9i

In article <mskvd.19305$i3.704754_at_weber.videotron.net>, Najm Hashmi says...
>
>Hi All,
>I need to migrate a SQL Sever Database to Oracle 9i. I am just curious what
>equivalent of SQL Server timestamp datatype in Oracle. Or how I can
>implement similar behavior in 9i. I am sure Oracle must have very elegant
>way to handle such situation.
>
>" timestamp is a data type that exposes automatically generated binary
>numbers, which are guaranteed to be unique within a database. timestamp is
>used typically as a mechanism for version-stamping table rows. The storage
>size is 8 bytes."
>
>Thanks in advance.
>
>Najm
>
>

So, you are doing lost update detection/prevention, you can:

alter table t add your_timestamp_column number; create sequence s;
create trigger t_trigger before inset or update on t for each row
begin
  select s.nextval into :new.t from dual; end;
/

Then you'll have a number field that is modified with a unique number everytime you insert/update the row...

Now, your updates just:

update t set .....
 where pk = :pk
   and t = :THE_VALUE_OF_T_WHEN_YOU_FETCHED_THE_ROW;

if that updates zero rows, someone else modified the data on you and you have to try again...

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corportation