trigger to check if a row can be inserted or not

[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.

[Jeanne Boyarsky]

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.

[ben oliver]

thanks Jeanne.

[Paul Campbell]

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]

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

[Jeanne Boyarsky]

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]

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]

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

Lucky you. I tend to fall asleep during meetings.

[ben oliver]

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]

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]

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]

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]

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]

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.