By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,369 Members | 1,240 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,369 IT Pros & Developers. It's quick & easy.

Auto updating field created from the value of an IDENTITY field

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
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

Nov 12 '05 #2

P: n/a
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.
Nov 12 '05 #3

P: n/a
>
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
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.