I have a problem to generate auto number by using triggering statement in primary key. The case is: I have a table: create table customer ( cust_no VARCHAR2(5) PRIMARY KEY, cust_name VARCHAR2(10), cust_addr VARCHAR2(20) );

The result from the table i want is: ----------------------- LU001 Lukas Adhi ... LU002 Lucky P ... AD001 Adi ... AD002 Adri ... ... ----------------------- if the cust_name is Lukas Adhi then the cust_no will LU001 (LU is get from 2 digit letter from cust_name, and 3 digits is sequence number). so my question is how to make auto generate cust_no like that, without using IDENTITY, and SEQUENCE. I want pure triggering statement, so please help me. Thank You!

-- Anonymous, September 28, 2004

Answers

Lukas,

You could do this with an INSTEAD OF INSERT trigger.

Hope this helps,

Eric

-- Anonymous, October 04, 2004