TRIGGERS
- From: Kean Jacinta <jacintakean@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 18 May 2005 00:16:56 -0700 (PDT)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
