| Raise_Application_ Error [message #372832] |
Mon, 12 March 2001 15:48  |
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 #372834 is a reply to message #372832] |
Mon, 12 March 2001 16:36  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Whenever you do a "select into", and no rows are returned, the exception NO_DATA_FOUND will be raised. You should include that in your exception handler. The TRUE at the end forces all errors to be shown, sometimes you only get your user defined message.
DECLARE
table_used EXCEPTION;
BEGIN
SELECT COUNT (*)
INTO m_tbl_cnt
FROM x_table
WHERE x_table.tbl_owner_id = :old.user_id;
RAISE table_used;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN table_used THEN
raise_application_error (-20001, 'Record in X_TABLE USED BY OTHERS, cant delete');
WHEN OTHERS THEN
raise_application_error (-20002, 'Delete trigger on X_TABLE failed with unspecified error', TRUE );
END;
|
|
|
|
| Re: Raise_Application_ Error [message #372835 is a reply to message #372832] |
Mon, 12 March 2001 20:11 |
Suresh
Messages: 189
Registered: December 1998
|
Senior Member |
|
|
Hi,
Code looks fine to me, it is doing what it is supposed to do.
Whenever it finds the matching rows in x_table it will raise the table_used exception.
Still if you any questions please let me know.
Thanks
Suresh
|
|
|
|
|
|
| Re: Raise_Application_ Error [message #372843 is a reply to message #372832] |
Tue, 13 March 2001 08:09 |
Mohamed Nassir
Messages: 3
Registered: March 2001
|
Junior Member |
|
|
Initially, I thought I was getting an error.
But, I just realised from checking around that this is exactly what Oracle is designed to do. The error is being raised and the message passed back. The rest of the stuff is purely there for advice for the developer, informing on what line the exception was raised and in which trigger it was raised.
So, the "error" that I thought it was is actually isn't an error. Thus, it is perfectly OK.
Sorry, for the false alarm. Thanks for all your responses.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
