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

Other related posts: