Re: Setting a Column Value on Insert

On 2 Aug 1997, Bob Carts wrote: } I have a need to make sure that a column is always set to a calculated } value when an insert occurs on the table. I don't want/can't depend on the } application coders to get it right. } } 1) Apparently, one can't influence columns values using an insert trigger: } create trigger example insert of customer } for each row (execute procedure nextseqno() into seqno); } } 2) The column default doesn't seem smart enough: } SEQNO INTEGER DEFAULT (select max(seqno) from customer), } or } SEQNO INTEGER DEFAULT (execute procedure nextseqno ()), } } 3) I am staying away from defining the column as a SERIAL data type becasue } it may be used elsewhere in the tables. } } 4) I would like to avoid forcing the application coders to do their insert } via a stored procedure if possible. } } Any ideas? } } -- } Bob Carts - SAIC } rcarts@agccs.lmco.com } 703-813-2914 } } Bob, Ideas, yes. Good ideas, I'm not sure. Here's a couple of ideas/hacks: 1. Be sure to lock the table first, insert max(seqno) + 1. Release exclusive lock. Shouldn't take too long. 2. Create a one row table with the starting sequence number, say 0. When you need a new number, lock the seqno table, as a transaction, increment the seqno, insert new seqno in other table, commit. Hope this helps. Nick ********************************* Nick Nobbe, Library of Congress NLS/BPH mail: nnob@loc.gov *********************************