Is it possible to define a serial datatype which autoincrements when updating a row?

Question

when a new row is added to a table containing a serial column, the next highest integer value is assigned to that column when the row is committed. can I define a serial datatype which will autoincrement when updating a row with a previously assigned serial value?...in datablade?.. I'm currently using the following functionality for an integer column "let intcol = select max(intcol) + 1 from table". In my app, when a customer makes an interest pymt, the previous ticket number gets updated with the next available ticket number.

Answer 1

For Oracle, no.

Typically done with a pre-insert trigger and sequence.

Answer 2

I think you would need an "AFTER UPDATE" trigger, possibly together with a sequence to avoid the overhead of counting max from the table.

I don't know anything about Informix and let's say I understand your "let intcol..." statement ;) But for example to recreate MySQL's auto increment functionality with Oracle tools you need code similar to this:

create sequence mytable_seq start with 1 increment 1;

create or replace trigger mytable_insert before insert 
for each row
begin
   select mytable_seq.nextval into :new.intcol from dual;
end;

Answer 3

From some of your other questions I gather you're using a pretty ancient version of Informix.

Relatively recent versions (10+, possibly slightly earlier) support SEQUENCE, which will do exactly what you're after:

CREATE SEQUENCE mytable_version 
INCREMENT BY 1 START WITH 1;

Then in your update statement:

UPDATE mytable
SET (payment, version) = (:pymt_amt, mytable_version.next_val)
WHERE ...

Every update will cause the version column to be updated with a new sequence number.

If your app has too many different UPDATE statements or access methods you can't control as well as you'd like, you could consider making the UPDATE to version occur as part of an UPDATE trigger.