TRIGGERS

Hi ,

I have been sending bit and pieces of question into this forum. I manage to go this far, but then i am stuck here again...

I have created :
table name : myclass
table column : id , name, type
primary key : id

sequence: myclassseq

This is my trigger body

BEGIN   IF INSERTING THEN

     IF :NEW.id IS NULL THEN
     SELECT myclassseq.NEXTVAL INTO :NEW.id FROM DUAL;
      
     END IF;
       

   END IF;    END; Currently the table contains values :

ID name type
--- ----- -----

1     BT      8
2     BI      8 
3     BA      8  

NOte : ID column is set as primary

Q1) I want to be able to insert into myclass table as well with this statement.

insert into myclass (id, name, type) values (2, 'BK' 8)

Since the ID 2 is alrdy existed , then it will prompt error stating that the column is unique. How to do a checking for the unique key ? This is to avoid the system generated ORA error messsages. If i can do checking in my trigger, before inserting then this error can be eliminated .

Q2) Maybe the above technique is not good enough. Can you please recommend the best practice i can follow to implement auto insert seq no into mytable with unique key checking.

Your help is very much appreciated . Thank YOu                   

Discover Yahoo!
Get on-the-go sports scores, stock quotes, news and more. Check it out! http://discover.yahoo.com/mobile.html
--

http://www.freelists.org/webpage/oracle-l Received on Wed May 18 2005 - 03:21:35 CDT