Oracle Trigger Error Logging
Contents |
March 2012 Oracle Magazine Online 2016 2015 2014 2013 2012 2011 2010 January 2012 March 2012 May 2012 July 2012 September 2012 oracle trigger example November 2012 As Published In March/April 2012 TECHNOLOGY: PL/SQL Error oracle compound trigger Management By Steven Feuerstein Part 6 in a series of articles on understanding and using PL/SQL
Oracle Trigger After Insert Example
Even if you write absolutely perfect PL/SQL programs, it is possible and even likely that something will go wrong and an error will occur when those programs
Oracle Trigger After Update
are run. How your code responds to and deals with that error often spells the difference between a successful application and one that creates all sorts of problems for users as well as developers. This article explores the world of error management in PL/SQL: the different types of exceptions you may encounter; when, why, and types of triggers in oracle how exceptions are raised; how to define your own exceptions; how you can handle exceptions when they occur; and how you can report information about problems back to your users. Exception Overview There are three categories of exceptions in the world of PL/SQL: internally defined, predefined, and user-defined. An internally defined exception is one that is raised internally by an Oracle Database process; this kind of exception always has an error code but does not have a name unless it is assigned one by PL/SQL or your own code. An example of an internally defined exception is ORA-00060 (deadlock detected while waiting for resource). A predefined exception is an internally defined exception that is assigned a name by PL/SQL. Most predefined exceptions are defined in the STANDARD package (a package provided by Oracle Database that defines many common programming elements of the PL/SQL language) and are among the most commonly encountered exceptions. One example is ORA-00001, which is assigned the name DUP_VAL_ON_INDEX in PL/S
a PL/SQL unit that is stored in the database and (if it is in the enabled state) automatically executes ("fires") in response to a specified event. A trigger has this structure: TRIGGER trigger_name triggering_event [
Oracle Triggers Tutorial
trigger_restriction ] BEGIN triggered_action; END; The trigger_name must be unique for triggers new or old references not allowed in table level triggers in the schema. A trigger can have the same name as another kind of object in the schema (for example, ora-04091 a table); however, Oracle recommends using a naming convention that avoids confusion. If the trigger is in the enabled state, the triggering_event causes the database to execute the triggered_action if the http://www.oracle.com/technetwork/issue-archive/2012/12-mar/o22plsql-1518275.html class="codeinlineitalic">trigger_restriction is either TRUE or omitted. The triggering_event is associated with either a table, a view, a schema, or the database, and it is one of these: DML statement (described in "About Data Manipulation Language (DML) Statements") DDL statement (described in "About Data Definition Language (DDL) Statements") Database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN) If the trigger is in the disabled state, the triggering_event https://docs.oracle.com/database/121/TDDDG/tdddg_triggers.htm does not cause the database to execute the triggered_action, even if the trigger_restriction is TRUE or omitted. By default, a trigger is created in the enabled state. You can disable an enabled trigger, and enable a disabled trigger. Unlike a subprogram, a trigger cannot be invoked directly. A trigger is invoked only by its triggering event, which can be caused by any user or application. You might be unaware that a trigger is executing unless it causes an error that is not handled properly. A simple trigger can fire at exactly one of these timing points: Before the triggering event executes (statement-level BEFORE trigger) After the triggering event executes (statement-level AFTER trigger) Before each row that the event affects (row-level BEFORE trigger) After each row that the event affects (row-level AFTER trigger) A compound trigger can fire at multiple timing points. For information about compound triggers, see Oracle Database PL/SQL Language Reference. An INSTEAD OF trigger is defined on a view, and its triggering event is a DML statement. Instead of executing the DML statement, Oracle Database executes the INSTEAD OF trigger. For more information, see "Creating an INSTEAD OF Trigger". A system trigger
ExpressionsReport Column PageResult SetSelect QuerySequenceSQL PlusStored Procedure FunctionSubquerySystem PackagesSystem Tables ViewsTableTable JoinsTriggerUser PreviliegeViewXMLLog a server error to a table : Database Trigger«Trigger«Oracle PL / http://www.java2s.com/Code/Oracle/Trigger/Logaservererrortoatable.htm SQLOracle PL / SQLTriggerDatabase TriggerLog a server error to a table SQL> SQL> CREATE TABLE error_log ( 2 timestamp DATE, 3 username VARCHAR2(30), 4 instance NUMBER, 5 database_name VARCHAR2(50), 6 error_stack VARCHAR2(2000) 7 ); Table created. SQL> SQL> CREATE OR REPLACE TRIGGER oracle trigger LogErrors 2 AFTER SERVERERROR ON DATABASE 3 BEGIN 4 INSERT INTO error_log 5 VALUES (SYSDATE, SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS. 6 DATABASE_NAME, DBMS_UTILITY.FORMAT_ERROR_STACK); 7 END LogErrors; 8 / Trigger created. SQL> SQL> SQL> BEGIN 2 -- This is a syntax error! 3 DELETE FROM students oracle trigger after 4 END; 5 / END; * ERROR at line 4: ORA-06550: line 4, column 4: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin case declare end exception exit for goto if loop mod null pragma raise return select update while with