| Trigger Help [message #151971] |
Tue, 20 December 2005 02:51  |
lchui
Messages: 2
Registered: December 2005
|
Junior Member |
|
|
I am facing some problem when inserting a new row to a table in Oracle 10g(RAC-clustering) running on HPUX. There is a AFTER-INSERT trigger defined on that table which is supposed to generate some unique sequence number for the profileId column.
Here is the schema:
create table profile_table (
profileId int,
name varchar(256),
domain int,
type int,
...
primary key(profileId));
CREATE OR REPLACE TRIGGER new_profile_trigger AFTER INSERT ON profile_table
DECLARE
GID NUMBER;
BEGIN
SELECT max(profileId)+1 INTO GID FROM profile_table;
UPDATE profile_table SET profileId=GID WHERE profileId=0;
END;
/
SHOW ERRORS;
The following statements are performed:
INSERT INTO profile_table(profileId, type, userId) values(0, 3, 'jdoe');
commit;
select profileId from profile_table;
I use JDBC to do the above insert and select. I don't get any complain after the insert. However after the select is executed, NULL is returned. If this select is executed again after some time, then the correct value is returned.
I am pretty new in this area. Does anyone know if this is a problem in my SQL or this is some known issue with Oracle10g? I haven't received any similar complain when this is run in Oracle10g on Windows.
Thanks,
Lisa
|
|
|
|
| Re: Trigger Help [message #151980 is a reply to message #151971] |
Tue, 20 December 2005 04:48  |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Hi,
Can you try doing the inserts from sqlplus and check if there is any problem? I tried from sqlplus and everything is working fine..I have oracle 9.2.0.6 on Solaris.I don't think that this issue has to do with version of OS or oracle..It might be something with jdbc but I do not know for sure..
By the way, using such code to generate primary key is very much less efficient compared to selecting such numbers from a sequence.
N.
|
|
|
|
| Re: Trigger Help [message #152050 is a reply to message #151980] |
Tue, 20 December 2005 13:40 |
lchui
Messages: 2
Registered: December 2005
|
Junior Member |
|
|
Niravshah,
Thank you very much for your reply.
Unfortunately, this problem only happens on customer's production site and I have no access to their environment. I have also tried this using Oracle 9i and couldn't spot any issue.
I have already researched on the triggger usage but don't find any obvious errors on it. Not sure if there is any concurrency issue.
My theory on this is the usage of trigger is not correct and I am thinking of eliminating this from our code. I am thinking of replacing the equivalent functionality with a another JDBC call before the insert:
1. "SELECT max(profileId)+1 INTO GID FROM profile_table;" and save to some variable X
2. "insert into profile_table (X, ...)"
What do you think? Will that cause any concurrency issue if multiple threads are exercising the same code?
Thanks,
Lisa
|
|
|
|
|
|
| Re: Trigger Help [message #152095 is a reply to message #152050] |
Wed, 21 December 2005 01:26 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Hi Lisa,
Since I do not know anything about jdbc, I could not help you there...However one point to explore is:
You say that , "Unfortunately, this problem only happens on customer's production site and I have no access to their environment..." So if the issue occurs in production environment then it MUST be duplicated in your QA and Development environments. You should try to define a repeatable test case , so that you can duplicate this behavior , in your Dev environment..
After defining this test case , you have to look into ways of debugging this out. This is a theoretical reply , sorry I could not help here...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
