-- Trigger to log all occurrances of a specific error
-- Copyright (c) 2004, Caleb.com

DROP TABLE bad_cons;
CREATE TABLE bad_cons (
   ts    date,
   usr   varchar2(32),
   stmt  long,
   stack varchar2(2000));


-- Fire on any server error, but only proceed if it's ORA-02292
CREATE OR REPLACE TRIGGER error_constraint
AFTER SERVERERROR ON DATABASE
DECLARE
   sql_text     ora_name_list_t;
   stmt         LONG;
   n            INTEGER;
BEGIN
   IF ( ora_is_servererror(2292) ) THEN
      -- Extract the original DDL statement and piece it together into a LONG
      n := ora_sql_txt( sql_text );
      FOR i IN 1..n LOOP
         stmt := stmt || sql_text(i);
      END LOOP;
      -- Log the error along with the call stack
      INSERT INTO bad_cons
         VALUES ( sysdate, ora_login_user, stmt, DBMS_UTILITY.FORMAT_ERROR_STACK);
   END IF;
END;
/

-- Notice what happens if the offending statement is issued from PL/SQL
-- instead of SQL...
--BEGIN
--   execute immediate 'delete from dept where deptno=20';
--END;
--/