On Error In Trigger Oracle
Contents |
TECHNOLOGY: Ask Tom The Trouble with Triggers By Tom Kyte Our technologist looks at trigger maintenance and implementation challenges. Those of you who frequent oracle compound trigger the asktom.oracle.com Web site know that I have an aversion to oracle trigger example triggers. Once upon a time, a long time ago, I thought triggers were the coolest thing ever and oracle trigger syntax I used (and abused) them heavily. Now, whenever possible, I will go very far out of my way to avoid a trigger. I have two primary reasons for
Oracle Trigger After Insert Example
avoiding triggers: They cause a long-term maintenance headache. Triggers are tiny bits of code that are not run directly by anything—they just “happen” as a side effect of some other operation. Because their work is done as a side effect, people frequently forget that triggers are there. (And reviewing code for all side effects is difficult, if oracle trigger after update not impossible.) Most times I see them implemented, they are implemented incorrectly. Triggers contain huge errors in logic that the developer didn’t see or anticipate, usually because that person didn’t expect the problems encountered. Maintenance Headache Hopefully the first reason to avoid triggers, the maintenance headache, is fairly easy to see. Suppose you are working on someone else’s project, because you have inherited someone else’s work. Say you have a bit of code or better yet a stored procedure that represents a transaction, and you read it. You might presume, “I understand what this does. I get it.” But if you are on a system laden with triggers all over the place, you won’t have gotten it at all—you’ll have gotten it wrong, at best. Triggers cause side effects. They do things out of the mainstream. When you run an update and see “1 row processed,” 500 other things may also have happened. More than once, I’ve received an e-mail similar to the following (this is a cut-and-paste—I did not make thi
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss types of triggers in oracle the workings and policies of this site About Us Learn more
Oracle Trigger When Clause
about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack
Compound Triggers In Oracle 10g
Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html each other. Join them; it only takes a minute: Sign up How to develop an after serverror trigger in Oracle? up vote 3 down vote favorite 2 I'm trying to log all the errors in my database into a table. So as user sys i wrote the following code: CREATE TABLE servererror_log ( error_datetime TIMESTAMP, error_user VARCHAR2(30), db_name http://stackoverflow.com/questions/536516/how-to-develop-an-after-serverror-trigger-in-oracle VARCHAR2(9), error_stack VARCHAR2(2000), captured_sql VARCHAR2(1000)); / CREATE OR REPLACE TRIGGER log_server_errors AFTER SERVERERROR ON DATABASE DECLARE captured_sql VARCHAR2(1000); BEGIN SELECT q.sql_text INTO captured_sql FROM gv$sql q, gv$sql_cursor c, gv$session s WHERE s.audsid = audsid AND s.prev_sql_addr = q.address AND q.address = c.parent_handle; INSERT INTO servererror_log (error_datetime, error_user, db_name, error_stack, captured_sql) VALUES (systimestamp, sys.login_user, sys.database_name, dbms_utility.format_error_stack, captured_sql); END log_server_errors; But when i force an error like trying to select from a non-existing table it doesn´t log the error in the table. Is there any way to check that the trigger fires at all? Also, I tried creating a test table to insert there but it doesn't work either, even if a define the trigger as an autonomous transaction and commit inside the trigger. Thanks, Joaquin oracle plsql triggers error-logging share|improve this question asked Feb 11 '09 at 12:14 xocasdashdash 332512 add a comment| 3 Answers 3 active oldest votes up vote 1 down vote Try this. You do not need to query v$sql, you can get the statement using ora_sql_txt. SQL> CREAT
one of the easiest is manipulating the :system.message_level:KEY-COMMIT - trigger on form-level (quick http://talk2gerd.blogspot.com/2006/12/best-practices-on-error-and-on-message.html and dirty)BEGIN :System.Message_Level := 25; COMMIT; :System.Message_Level := 5;END;or KEY-COMMIT - triggerDECLARE V_Message_Level NUMBER;BEGIN V_Message_Level := :System.Message_Level; :System.Message_Level := 25; COMMIT; :System.Message_Level := V_Message_Level;END;these are not best practices. Because you have to write those codes in hundreds of procedures and tons of code.Filtering the errors and messages oracle trigger is the key to have a powerful message-handling. Here is an easy procedure which shows you the technique:ON-ERROR - trigger on form-levelDECLARE V_Error_Code NUMBER; V_Error_Text VARCHAR2 (2000); V_DBMS_Error_Code NUMBER; V_DBMS_Error_Text VARCHAR2 (2000);BEGIN V_Error_Code := Error_Code; V_Error_Text := Error_Text; V_DBMS_Error_Code := DBMS_Error_Code; V_DBMS_Error_Text := DBMS_Error_Text; IF V_Error_Code IN (40401, oracle trigger after 40405) THEN /* || 40401, 40405 - no changes to save / apply get filtered */ NULL; ELSIF V_Error_Code IN (-1034, -3114) THEN /* || -1034, -3114 - not connected to database */ Message ('Not connect to database, exiting Form'); Exit_Form (no_validate); ELSIF V_Error_Code IN (40508, 40735) AND V_DBMS_Error_Code BETWEEN -20999 AND -20000 THEN /* || -20000 errors are raised by RAISE_APPLICATION_ERROR || They are handled in a different way */ Show_and_Log_DB_Error (V_DBMS_Error_Text); ELSE /* || All other errors went into Show_and_Log_Error, where they || get inspected, analyzed and logged. */ Show_and_Log_Error (V_Error_Code); END IF;END; 4 comments: Anonymous said... Hello sir,I tried the same code in on-error trigger in forms but showing me compilation errorthe identifier'Show_and_Log_DB_Error 'and Show_and_Log_Error'must be declared January 16, 2008 Gerd Volberg said... that's correct. Those routines point to your logging-procedures. If you want to create su