File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes trigger to check if a row can be inserted or not Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "trigger to check if a row can be inserted or not" Watch "trigger to check if a row can be inserted or not" New topic
Author

trigger to check if a row can be inserted or not

ben oliver
Ranch Hand

Joined: Mar 28, 2006
Posts: 375
Hi,

I am learning to write oracle sql trigger. I have a table

STUDENT(ID, name, address, phone), where ID is from STUDENT_SEQ oracle sequence and it is the PK. where I want to make sure is --

when a new row is going to be inserted into the table, first check if there is already a similar row exsiting there. If yes, then don't insert. How about this trigger

create or replace trigger BI_CHECK_EXIST
BEFORE INSERT
if( ** there is already a row whose "name", "address", "phone" is in the table **) THEN
RAISE_EXCEPTION_ERROR(..)
end if;
End;

Does this make sense ? But how to write

** there is already a row whose "name", "address", "phone" is in the table *

?? I have no idea..

Any help will be appreciated.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30919
    
158

Ben,
Do you need to use a trigger? You could add a unique constraint to the table and let it handle this logic for you.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
ben oliver
Ranch Hand

Joined: Mar 28, 2006
Posts: 375
thanks Jeanne.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by ben oliver:
Hi,

I am learning to write oracle sql trigger. I have a table

STUDENT(ID, name, address, phone), where ID is from STUDENT_SEQ oracle sequence and it is the PK. where I want to make sure is --

when a new row is going to be inserted into the table, first check if there is already a similar row exsiting there. If yes, then don't insert. How about this trigger

create or replace trigger BI_CHECK_EXIST
BEFORE INSERT
if( ** there is already a row whose "name", "address", "phone" is in the table **) THEN
RAISE_EXCEPTION_ERROR(..)
end if;
End;

Does this make sense ? But how to write

** there is already a row whose "name", "address", "phone" is in the table *

?? I have no idea..

Any help will be appreciated.


Not entirely Ben (and Jeanne but then it has been a two very long two week sprints for installs and I am a bit sleep deprived so I may just not be understanding any thing I read right now. ) since it is the PK, there is already a primary key constraint. Your trigger should look more like this:

CREATE OR REPLACE TRIGGER yourtrigger
BEFORE INSERT
ON yourtable
FOR EACH ROW

BEGIN
/** you may want to test to see if the NEW.yourcolumn is null before execution **/

SELECT yourseq.NEXTVAL
INTO:NEW.yourcolumn
FROM dual;
END primKey;
/

In your pl/sql program that performs your inserts you would need to handle primary key exceptions. The easiest way is to grab the next sequence and test to see if it exists as a key in your table by using an exception block that test for no_data found.

normally, if you are setting your own key... you would get your next sequence, store it to a variable, and test to see if the value exists by doing a select statement using your new key as your where clause with an exception block that tests for when_no_data_found. in the exception for when no_data_found you would insert your row with the key you just tested with your select.

EXCEPTION

WHEN NO_DATA_FOUND THEN

You could do the same thing in your trigger... but triggers need to be as effecient as possible so you really should do this in your application logic.

Alternatively, you could do something with the DUP_VAL_ON_INDEX exception.

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

do some other insert logic here to increment to an appropriate value and remember you can nest exception blocks or raise an error;

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(´┐ŻError on Insert´┐Ż || SQLERRM);

END;
/

[ January 10, 2008: Message edited by: Paul Campbell ]
[ January 10, 2008: Message edited by: Paul Campbell ]
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

Paul, why would we prefer using a trigger in stead of constraint?
Would it not be more straightforward to use the declarative unique constraint, rather than the trigger?

Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30919
    
158

Paul,
I didn't see the bit about it being the primary key. My unique constraint comment was if you needed to see the whole row is unique. If all Ben needs is primary key uniqueness, I agree with Jan that the database handles it already.

I also don't see the benefit of a trigger here in either case.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Jan Cumps:
Paul, why would we prefer using a trigger in stead of constraint?
Would it not be more straightforward to use the declarative unique constraint, rather than the trigger?

Regards, Jan


Hey Jan,

It was the eleventh 18 hour day in a row for me and I misread the post... I thought the poster was inserting the Primary Key... but now after 4 hours sleep (I'll be glad when this project ends), I can see Ben was wanting to check for duplicate data prior to inserting a new key/row and Jeanne (and you) are both correct, you could do a multi-column unique constraint.

Sometimes when sleep deprived, I become an "over-thinker".

Paul
[ January 11, 2008: Message edited by: Paul Campbell ]
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

Sometimes when sleep deprived, I become an "over-thinker".
Lucky you. I tend to fall asleep during meetings.
ben oliver
Ranch Hand

Joined: Mar 28, 2006
Posts: 375
Hi Folks,I am very touched that you guys could jump in to help even you miss so much sleep ! Sorry about that and I appreciate it ! Back to the issue, yes, I was not concerned about the PK uniqueness. I was trying adding an extra constraint to make several more columns (non of them is PK) to be unique due to current business logics. but I still want to use sequence id as the PK anyway.

Now folks, I am using Oracle 10g XE as many people are using. I found when I create constraint and select "unique" it only allows me to pick 3 columns !! Is this a restriction of 10g XE product or what ? How many column does oracle allow us to specify for such unque constraint ?

Thanks.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Jan Cumps:
Lucky you. I tend to fall asleep during meetings.


I live on red bull and rock star energy drinks.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by ben oliver:

Now folks, I am using Oracle 10g XE as many people are using. I found when I create constraint and select "unique" it only allows me to pick 3 columns !! Is this a restriction of 10g XE product or what ? How many column does oracle allow us to specify for such unque constraint ?

Thanks.


It must be a an XE limitation... Oracle supports 32 columns for the unique key.
ben oliver
Ranch Hand

Joined: Mar 28, 2006
Posts: 375
1. In some of the tutorial, I saw they just use

CONSTRAINT my_const UNIQUE(col-1, col_2);

But in some document I saw it looks like

CONSTRAINT another_const PRIMARY KEY ("MY_ID") ENABLE

Do I need to put the syntax of "ENABLE" ??

Thanks
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

Originally posted by ben oliver:
Do I need to put the syntax of "ENABLE" ??
No. The ENABLE clause serves to re-enable constraints that you have disabled before.
This article on TECH on the Net explains it nicely.
[ January 11, 2008: Message edited by: Jan Cumps ]
ben oliver
Ranch Hand

Joined: Mar 28, 2006
Posts: 375
Originally posted by Jan Cumps:
No. The ENABLE clause serves to re-enable constraints that you have disabled before.
This article on TECH on the Net explains it nicely.

[ January 11, 2008: Message edited by: Jan Cumps ]


Oh my, so if this is the first time I create this constraint and I have the constraint there, does it hurt ??

I saw this "ENABLE" was created by the script generation tool. Anyway, please confirm with me --- Suppose this is the first time I try to load and execute this SQL DDL script, does it hurt if I have "ENABLE" keyword there ??

Thanks.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: trigger to check if a row can be inserted or not