OraFAQ Forum: SQL & PL/SQL » Exception at Update Trigger

Home » SQL & PL/SQL » SQL & PL/SQL » Exception at Update Trigger (11G, Win 7)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Exception at Update Trigger [message #563480]

Tue, 14 August 2012 05:23

rj_pbu
Messages: 1
Registered: August 2012

Junior Member

Hi,

I am writing a trigger TR_EMP on a table EMP which has columns EMP_ID, EMP_NAME, ALT_EMP_ID.

Now I am updating ALT_EMP_ID for an EMP_ID(PK) which is unique.

If ALT_EMP_ID is null
then
:new.ALT_EMP_ID = l_alt_emp_id;
end if;

As this ALT_EMP_ID is unique, same ID shouldn't be inserted again here. When data being inserted with 2 different sessions for 2 different EMP_ID there is a possible chance of inserting same ALT_EMP_ID for both which results in Unique error. I need to handle this exception. DUP_VAL_ON_INDEX or OTHERS Execption not able to handle this.

Can you help me out on how to handle such exception?

Report message to a moderator

Re: Exception at Update Trigger [message #563483 is a reply to message #563480]

Tue, 14 August 2012 05:31

Littlefoot
Messages: 16934
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How do you calculate L_ALT_EMP_ID?
Could you consider using a sequence instead?

Report message to a moderator

Re: Exception at Update Trigger [message #563495 is a reply to message #563480]

Tue, 14 August 2012 07:57

joy_division
Messages: 4265
Registered: February 2005
Location: East Coast USA

Senior Member

rj_pbu wrote on Tue, 14 August 2012 06:23

I need to handle this exception. DUP_VAL_ON_INDEX or OTHERS Execption not able to handle this.

Can you help me out on how to handle such exception?

How is it possible that DUP_VAL_IN_INDEX is not able to handle this? You must be doing it wrong.
You are correct that OTHERS does not, as it is a bug to have it in your code.

Report message to a moderator

Re: Exception at Update Trigger [message #563496 is a reply to message #563480]

Tue, 14 August 2012 08:08

flyboy
Messages: 1669
Registered: November 2006

Senior Member

From https://forums.oracle.com/forums/thread.jspa?threadID=2426984&tstart=0
kendenny

The trigger isn't going to get the exception. The code that contains the "INSERT" statement is where the error needs to be handled.

The whole approach seems to be flawed. Instead of fixing it, you should rather state the requirements and start from the scratch using proper design/implementation. As you did not post them here, this is all I can say.
Of course, using sequence for assigning unique values is the best technique.

Report message to a moderator

Re: Exception at Update Trigger [message #563534 is a reply to message #563496]

Tue, 14 August 2012 12:22

joy_division
Messages: 4265
Registered: February 2005
Location: East Coast USA

Senior Member

flyboy wrote on Tue, 14 August 2012 09:08

From https://forums.oracle.com/forums/thread.jspa?threadID=2426984&tstart=0
kendenny
The trigger isn't going to get the exception. The code that contains the "INSERT" statement is where the error needs to be handled.

Oh boy, rookie mistake by me. Even though the title says "...update trigger," I assumed you were inserting. I should stop answering questions today because it looks like it is going to be "one of those days."

Report message to a moderator

Previous Topic:	What am I doing wrong here...
Next Topic:	CTE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 20:13:42 CDT 2013

Total time taken to generate the page: 0.69672 seconds