Home » SQL & PL/SQL » SQL & PL/SQL » Trigger problem??
Trigger problem?? [message #232571] Sat, 21 April 2007 06:01 Go to next message
nafe74
Messages: 7
Registered: April 2007
Junior Member
I am trying to implement a trigger where if an id is specified an
internal version is auto generated, but if neither is specified they
both get generated. It works fine for when neither is specified, but
if the id is specified i get the following error, I am using a package
to run the functions that do this and that code can also be found below:

ORA-20802: Application_Trigger [ORA-20901: Unexpected error in
ID_Exists [1]]
ORA-06512: at "HR.APPLICATION_TRIGGER", line 37
ORA-04088: error during execution of trigger 'HR.APPLICATION_TRIGGER'

CREATE OR REPLACE PACKAGE APP_COMMON
IS

FUNCTION ID_Exists (P_Table IN VARCHAR2,
P_Id IN NUMBER)
RETURN BOOLEAN ;
--
-- Function to return the next available internal version number for a
-- given table for the specified ID.
--
FUNCTION Next_Version (P_Table IN VARCHAR2,
P_Id IN NUMBER)
RETURN NUMBER ;


END APP_COMMON ;
/

SHO ERR

CREATE OR REPLACE PACKAGE BODY APP_COMMON
IS
FUNCTION ID_Exists (P_Table IN VARCHAR2,
P_Id IN NUMBER)
RETURN BOOLEAN
IS
TYPE t_ref_cursor IS REF CURSOR ;

C_ID_Exists t_ref_cursor ;
w_dummy NUMBER(1) ;
w_result BOOLEAN ;

BEGIN
OPEN C_ID_Exists FOR 'SELECT 1 FROM ' || P_Table || ' WHERE ID =
:B_Id ' USING p_Id ;
FETCH C_ID_Exists INTO w_dummy ;

w_result := C_ID_Exists%FOUND = TRUE ;

CLOSE C_ID_Exists ;

RETURN w_result ;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20901,'Unexpected error in
ID_Exists [' || TO_CHAR(P_ID) || ']') ;
END ID_Exists ;

--
-- Function to return the next available internal version number for a
-- given table for the specified ID.
--
FUNCTION Next_Version (P_Table IN VARCHAR2,
P_Id IN NUMBER)
RETURN NUMBER
IS
TYPE t_ref_cursor IS REF CURSOR ;

C_Next_Id t_ref_cursor ;
w_version NUMBER(9) ;

BEGIN
OPEN C_Next_Id FOR 'SELECT MAX(Internal_Version) FROM ' ||
P_Table || ' WHERE ID = :B_Id ' USING p_Id ;
FETCH C_Next_Id INTO w_version ;

CLOSE C_Next_Id ;
--
-- Return the max version plus 1 (the next available value)
RETURN w_version + 1;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20902,'Unexpected error in
Next_Version [' || TO_CHAR(P_ID) || ']') ;
END Next_Version ;

END APP_COMMON ;
/

The trigger being used is :

CREATE OR REPLACE TRIGGER Application_Trigger BEFORE INSERT ON Application FOR EACH ROW
DECLARE
w_nextval Application.App_ID%TYPE ;

BEGIN
--
-- If the ID is specified then don't bother generating a new one
IF :NEW.App_ID IS NOT NULL
THEN
--
-- Becuase it's been specified, it must already exist. If not
-- raise an error.
--
IF APP_COMMON.ID_Exists ('APPLICATION',:NEW.App_ID)
THEN
--
-- The ID already exists so calculate the next available
-- internal version.
:NEW.Internal_Version := APP_COMMON.Next_Version('APPLICATION',:NEW.App_ID) ;
ELSE
RAISE_APPLICATION_ERROR (-20801,'ID Must exist if specified [' || TO_CHAR(:NEW.App_ID) || ']') ;
END IF ;
ELSE
--
-- The ID's not specified so generate the next one and default the
-- internal version to 1
--
SELECT Application_SEQ.NEXTVAL
INTO w_nextval
FROM DUAL ;

:NEW.App_ID := w_nextval ;
:NEW.Internal_Version := 1 ;
END IF ;

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20802,'Application_Trigger [' || SQLERRM || ']') ;
END ;
/

Any ideas??

Thanks,

Nath
Re: Trigger problem?? [message #232573 is a reply to message #232571] Sat, 21 April 2007 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This will never work.
I don't talk about your trigger (code can be corrected), I talk about logic.
For instance, what do you think will happen if 2 users insert the same id (that exists or not)?

Regards
Michel
Re: Trigger problem?? [message #232575 is a reply to message #232571] Sat, 21 April 2007 06:37 Go to previous messageGo to next message
nafe74
Messages: 7
Registered: April 2007
Junior Member
sorry i probably haven't explained this very well the id and internal version make up a joint primary key, so if neither is specified they both get set to 1. However if someone specifies the id i want the internal version to be automatically set to 2 then 3 etc.

Re: Trigger problem?? [message #232583 is a reply to message #232575] Sat, 21 April 2007 08:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, I understand but you don't answer my question.
Read your code and tell what happens if 2 users try to insert id=42 (assuming it exists) at the same time?

Regards
Michel
Re: Trigger problem?? [message #232587 is a reply to message #232571] Sat, 21 April 2007 09:57 Go to previous messageGo to next message
nafe74
Messages: 7
Registered: April 2007
Junior Member
But surely if I lock out the tables when doing an insert so only one person can insert at the same time, then this would be possible?
Re: Trigger problem?? [message #232588 is a reply to message #232587] Sat, 21 April 2007 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't buy Oracle to do that.
Just use plain file, it is free.

Regards
Michel
Re: Trigger problem?? [message #232589 is a reply to message #232571] Sat, 21 April 2007 11:09 Go to previous messageGo to next message
nafe74
Messages: 7
Registered: April 2007
Junior Member
I thought this forum was meant to help people?

Is anybody else out there able to give any views on this problem?

Thanks
Re: Trigger problem?? [message #232591 is a reply to message #232571] Sat, 21 April 2007 11:56 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Is anybody else out there able to give any views on this problem?
With free advice you get what you paid for it.
If you don't like any answer you are entitled to a full refund.
You've ignored the #1 & #2 Sticky posts about how code should be posted (using "code tags") & what ancillary information should be included.
You're On Your Own (YOYO)!

[Updated on: Sat, 21 April 2007 12:36] by Moderator

Report message to a moderator

Re: Trigger problem?? [message #232592 is a reply to message #232589] Sat, 21 April 2007 13:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition to Ana post: We (at least I but I think many of experiented Oraclees) are not posting here to encourage or help you in a dead-end and don't want it.
For myself, I'll never help you to debug something I know it is wrong and will never work.
I don't know why you insist to do that but if instead of whining you tell us your business requirement maybe we can help you to get a correct design.

Regards
Michel
Re: Trigger problem?? [message #232870 is a reply to message #232571] Mon, 23 April 2007 13:45 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Why do you want to create such an unworkable schema. Simply create an oracle sequence and use the sequence as your unique internal key. Since no one will ever see it, who cares if it is a guaranteed unique value. Use the one column as your primary key. Setup an index on the external ID so it can be found quickley.
Previous Topic: Read BLOB data using PLSQL
Next Topic: what is the differnece between MS-access and oracle databases
Goto Forum:
  


Current Time: Tue Dec 06 04:52:49 CST 2016

Total time taken to generate the page: 0.06753 seconds