StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The simple answer is No - but it's easy to create one yourself with a NUMBER column and a trigger to set/update it.</p> <p>A simple example for Oracle 11gR2:</p> <pre><code>CREATE SEQUENCE global_rowversion_seq; ALTER TABLE mytable1 ADD rowversion NUMBER; ALTER TABLE mytable2 ADD rowversion NUMBER; CREATE TRIGGER mytable1_biu BEFORE INSERT OR UPDATE ON mytable1 FOR EACH ROW BEGIN :NEW.rowversion := global_rowversion_seq.NEXTVAL; END mytable1_biu; CREATE TRIGGER mytable2_biu BEFORE INSERT OR UPDATE ON mytable2 FOR EACH ROW BEGIN :NEW.rowversion := global_rowversion_seq.NEXTVAL; END mytable2_biu; </code></pre> <p>(If you're on an earlier Oracle version, the assignments in the triggers must be done with a query, e.g.:</p> <pre><code> SELECT global_rowversion_seq.NEXTVAL INTO :NEW.rowversion FROM dual; </code></pre> <p>Now, keep in mind in some cases this design may have a performance impact in extreme situations (e.g. databases with extremely high insert/update activity) due to contention from all database inserts/updates using the same sequence. Of course, in this circumstance you probably would avoid triggers in the first place anyway.</p> <p>Depending on how you use the rowversion column, it may be a good idea to use a separate sequence for each table instead. This would mean, of course, that rowversion would no longer be globally unique - but if you are only interested in comparing changes to rows within a table, then this would be fine.</p> <p>Another approach is to advance the counter for each row individually - this doesn't need a sequence and allows you to detect changes to a row (but does not allow comparing any row to another row):</p> <pre><code>ALTER TABLE mytable ADD rowversion NUMBER; CREATE TRIGGER mytable_biu BEFORE INSERT OR UPDATE ON mytable FOR EACH ROW BEGIN :NEW.rowversion := NVL(:OLD.rowversion, 0) + 1; END mytable_biu; </code></pre> <p>Each row will be inserted with rowversion = 1, then subsequent updates to that row will increment it to 2, 3, etc.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload