Home » SQL & PL/SQL » SQL & PL/SQL » Exception at Update Trigger (11G, Win 7)
Exception at Update Trigger [message #563480] Tue, 14 August 2012 05:23 Go to next message
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?
Re: Exception at Update Trigger [message #563483 is a reply to message #563480] Tue, 14 August 2012 05:31 Go to previous messageGo to next message
Littlefoot
Messages: 19677
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?
Re: Exception at Update Trigger [message #563495 is a reply to message #563480] Tue, 14 August 2012 07:57 Go to previous messageGo to next message
joy_division
Messages: 4527
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.
Re: Exception at Update Trigger [message #563496 is a reply to message #563480] Tue, 14 August 2012 08:08 Go to previous messageGo to next message
flyboy
Messages: 1775
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.
Re: Exception at Update Trigger [message #563534 is a reply to message #563496] Tue, 14 August 2012 12:22 Go to previous message
joy_division
Messages: 4527
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."
Previous Topic: What am I doing wrong here...
Next Topic: CTE
Goto Forum:
  


Current Time: Mon Oct 20 04:02:37 CDT 2014

Total time taken to generate the page: 0.05520 seconds