| Raise_Application_ Error [message #372833] |
Mon, 12 March 2001 15:49  |
Mohamed Nassir
Messages: 3
Registered: March 2001
|
Junior Member |
|
|
I am having some problems with trigger use in Oracle. I suspect it's got to do with the error handling function Raise_Application_Error.
Here is my trigger code....
CREATE OR REPLACE TRIGGER X_DEL_USER
-- i am trying to put a trigger to prevent
-- deletion of user if M_TBL_CNT > 0
BEFORE DELETE ON X_USER
FOR EACH ROW
DECLARE M_TBL_CNT NUMBER(7);
TABLE_USED EXCEPTION;
BEGIN
SELECT COUNT(*) INTO M_TBL_CNT
FROM X_TABLE
WHERE X_TABLE.TBL_OWNER_ID = :OLD.user_ID;
IF (M_TBL_CNT > 0) THEN
RAISE TABLE_USED;
END IF;
EXCEPTION
WHEN TABLE_USED THEN
RAISE_APPLICATION_ERROR(-20001,'TABLE USED BY OTHERS');
END;
... however when I execute this trigger and I perform the delete function, i get the following msgs:
SQL> DELETE FROM X_USER WHERE USER_ID=5001;
DELETE FROM X_USER WHERE USER_ID=5001
*
ERROR at line 1:
ORA-20001: TABLE USED BY OTHERS
ORA-06512: at "CMBC4002.X_DEL", line 7
ORA-04088: error during execution of trigger 'CMBC4002.X_DEL'
It seems to me that Oracle is having difficulty understanding the function raise_application_error.
I hope someone can help. Thanks.
Mohamed Nassir
|
|
|
|
| Re: Raise_Application_ Error [message #372909 is a reply to message #372833] |
Fri, 16 March 2001 20:42  |
Nilakshi
Messages: 1
Registered: March 2001
|
Junior Member |
|
|
|
To me, it looks as if you are encountering the exception TABLE_USED, because of which it is displaying the text you have put in RAISE_APPLICATION_ERROR. So, in short your M_TBL_CNT > 0 condition is evaluating to TRUE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
