Auto updating field created from the value of an IDENTITY field

[Poul Møller Hansen]

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

[Jean-David Beyer]

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

[Christian Maslen]

Poul,

A sequence offers more flexibility. Apart from being able to convert
to chars, you can apply check digits and other business requirements
for keys. The following is a demo. I've made an assumption about the
maximum value of your key. If it doesn't hold, you can alter the
trigger.

-- create the sequence with a start value matching your digit count.
CREATE SEQUENCE SQ_CHARKEY_BASE
START WITH 1000001
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
CACHE 24;

-- table to demo its use in a trigger.
CREATE TABLE TABLE1 (
TABLE_ID CHAR(10) NOT NULL
,COL1 VARCHAR (30) NOT NULL
);

ALTER TABLE TABLE1 ADD
CONSTRAINT PK_TABLE1 PRIMARY KEY
(
TABLE_ID
)
;

-- Use trigger to automatically assign the key. You can create a UDF
-- if you need the value before inserting.
CREATE TRIGGER BIT1_TABLE1
NO CASCADE BEFORE INSERT
ON TABLE1
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.TABLE_ID IS NULL)
BEGIN ATOMIC
SET N.TABLE_ID = 'N' || SUBSTR( CAST(NEXTVAL FOR
SQ_CHARKEY_BASE AS CHAR(10)), 2);
END@

-- test it.
INSERT INTO TABLE1 (COL1)
VALUES ('VAL1'),
('VAL2'),
('VAL3');

SELECT *
FROM TABLE1;
Christian.

[Poul Møller Hansen]

>

A sequence offers more flexibility. Apart from being able to convert
to chars, you can apply check digits and other business requirements
for keys. The following is a demo. I've made an assumption about the
maximum value of your key. If it doesn't hold, you can alter the
trigger.

Christian, this was exactly what I am looking for.
Thank you very much.

Poul