Re: Setting a Column Value on Insert

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 Don't allow the coders to be dumb - make them learn the right way to do things. If you're saying a certain attribute is really required, then make the coders understand and adhere to the correct model. Instead of trying to provide the value, generate an error so the coders will learn what's wrong and correct it. Instead of generating the value, use a trigger to invoke a stored procedure that checks if a value was specified (ie. is not null) and raises an exception if not. Now, if the value needs to somehow be calculated from other values in the row, then you'll need to figure out how to make sure all applications do it the same way (ideally using the same piece of code whether that be a host language like C or stored procedure language, etc. ----------- Roger Tomas AG Communication Systems