Poul Møller Hansen wrote:
Hi,
I need an auto incrementing field that will contain values like
N000001, N000002, N000003 etc.
I think the way is to use the value from an identity field in a stored
procedure that is triggered at insert.
I can't see that it can be made in pure SQL, but Java is not a problem.
Any of you that can tell me the way of doing it ?
Thanks
Poul
You mean like this?
-- This table is used to contain the counters used to generate
-- serial-numbered entries for other tables. The table and column
-- names are in here, as well as the value of the counter. Programs
-- are assumed to increment the value after obtaining it. They
-- should obtain the value and increment it in a single transaction.
CREATE TABLE serial (
serial_table CHAR(30) NOT NULL,
serial_column CHAR(30) NOT NULL,
serial_value INTEGER NOT NULL,
PRIMARY KEY (serial_table, serial_column)
);
-- Initialize the serial table.
INSERT INTO serial (serial_table, serial_column, serial_value)
VALUES ('company', 'company_id', 10001),
('source', 'source_id', 101);
-- This table contains the unique company identifier (company_id) for
-- every company in the database. We would have preferred to use the
-- CRSP PERMNO instead, but we do not know it for every company.
CREATE TABLE company (
company_id INTEGER NOT NULL,
company_name CHAR(40) NOT NULL,
PRIMARY KEY (company_id)
);
CREATE TRIGGER company_insert
AFTER INSERT ON company
FOR EACH ROW MODE DB2SQL
UPDATE serial
SET serial_value = serial_value + 1
WHERE serial_table = 'company'
AND serial_column = 'company_id';
-- This table is used to map the name of the source of data with an
-- integer that will be used in other tables to make them smaller.
CREATE TABLE source (
source_id INTEGER NOT NULL,
source_name CHAR(40) NOT NULL,
PRIMARY KEY (source_id)
);
CREATE TRIGGER source_insert
AFTER INSERT ON source
FOR EACH ROW MODE DB2SQL
UPDATE serial
SET serial_value = serial_value + 1
WHERE serial_table = 'source'
AND serial_column = 'source_id';
--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 73926.
/( )\ Shrewsbury, New Jersey
http://counter.li.org
^^-^^ 3:00pm up 12 days, 3:44, 3 users, load average: 4.07, 4.04, 4.00