| Trigger problem?? [message #232571] |
Sat, 21 April 2007 06:01  |
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 #232575 is a reply to message #232571] |
Sat, 21 April 2007 06:37  |
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 #232591 is a reply to message #232571] |
Sat, 21 April 2007 11:56 |
 |
BlackSwan
Messages: 26766
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 #232870 is a reply to message #232571] |
Mon, 23 April 2007 13:45 |
Bill B
Messages: 1971
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
