Home » SQL & PL/SQL » SQL & PL/SQL » Triggers fail to fire (merged 3)
Triggers fail to fire (merged 3) [message #383103] Tue, 27 January 2009 04:43 Go to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
Hi! I'm having trouble with an after server error trigger. Until 2 weeks ago the trigger worked flawlessly logging all the errors generated by the user. Today I started to implement this same functionality in a new schema but it fails to fire.
Here is my code
drop table error_log;
  /
  CREATE TABLE ERROR_LOG 
   (	ID_ERROR_LOG NUMBER(*,0), 
	CODIGO_ERROR NUMBER(*,0) NOT NULL ENABLE, 
	MENSAJE_DEL_ERROR VARCHAR2(4000 BYTE) NOT NULL ENABLE, 
	FECHA_CREACION DATE DEFAULT sysdate, 
	TIMESTAMP_CREACION TIMESTAMP (6) DEFAULT systimestamp, 
	CREADO_POR VARCHAR2(100 BYTE) DEFAULT USER, 
	ERROR_STATEMENT NUMBER NOT NULL ENABLE, 
	SQL_STATEMENT CLOB NOT NULL ENABLE, 
	 CONSTRAINT PK_ERROR_LOG PRIMARY KEY (ID_ERROR_LOG));
  /
  drop sequence ERROR_STATEMENT_SEQ;
  /
  drop sequence ERROR_LOG_SEQ;
  /
  create sequence ERROR_STATEMENT_SEQ start with 1 increment by 1 nomaxvalue minvalue 0 cache 20;
  /
  create sequence ERROR_LOG_SEQ start with 1 increment by 1 nomaxvalue minvalue 0 cache 20;
  /  
create table temporal( example varchar2(2000));
/

  create or replace TRIGGER TRIG_ASERVERERROR
AFTER SERVERERROR ON SCHEMA
DECLARE
  ID_ERROR NUMBER;
  ID_STATEMENT NUMBER;
  CODIGO_ERROR NUMBER;
  MENSAJE_ERROR VARCHAR2(2000); 
  sql_statement clob := empty_clob();
  sql_text ora_name_list_t;
  len binary_integer :=0;
BEGIN  
  
  insert into temporal(example) values ('ejemplo');

  for i in 1..ORA_SERVER_ERROR_DEPTH 
  loop
    IF is_servererror(0) THEN
      null;
    else       
      IF I = 1 THEN
        SELECT ERROR_STATEMENT_SEQ.NEXTVAL
        INTO ID_STATEMENT 
        FROM DUAL;
      END IF;
      
      dbms_lob.createtemporary(sql_statement, TRUE);
      dbms_lob.open(sql_statement, dbms_lob.lob_readwrite);  
      
      CODIGO_ERROR := ora_server_error(i);
      MENSAJE_ERROR := ORA_SERVER_ERROR_MSG(i);
      
      SELECT ERROR_LOG_SEQ.NEXTVAL 
      INTO ID_ERROR 
      FROM DUAL;
      
      for i in 1..ora_sql_txt(sql_text) 
      loop
        len := length(sql_text(i));
        dbms_lob.writeappend(sql_statement, len, sql_text(i));    
      end loop;
      dbms_lob.close(sql_statement);
          
      INSERT INTO ERROR_LOG(id_error_log, codigo_error, mensaje_del_error, error_statement, sql_statement) 
      VALUES( ID_ERROR, CODIGO_ERROR,MENSAJE_ERROR, id_statement, sql_statement);
      
    END IF;      
  end loop;  
exception 
  when others then
      dbms_lob.close(sql_statement);  
END TRIG_ASERVERERROR;  
/


I'm using SQL Developer to develop this code. Do you see any problem with it? Can I provide any more information?
Re: Triggers fail to fire (merged 3) [message #383112 is a reply to message #383103] Tue, 27 January 2009 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost your question, patiently wait and retry the forum to see if your question is there.
You don't need to post it 3 times in 6 minutes.

Regards
Michel


[Updated on: Tue, 27 January 2009 05:21]

Report message to a moderator

Re: Triggers fail to fire (merged 3) [message #383117 is a reply to message #383103] Tue, 27 January 2009 05:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It will be firing - I guess it will be erroring somewhere, and the When Others clause will be trapping the error, and completely ignoring it.

You need error logging in your error logging code - add an INSERT INTO ERRORLOG.... into the When Others clause, and use that to log the actual Sql error that is being raised in your code.
Re: Triggers fail to fire (merged 3) [message #383260 is a reply to message #383117] Wed, 28 January 2009 00:55 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
I' ve done the modification you suggested. Here is the new trigger code:
create or replace
TRIGGER TRIG_ASERVERERROR
AFTER SERVERERROR ON SCHEMA
DECLARE
  ID_ERROR NUMBER;
  ID_STATEMENT NUMBER;
  CODIGO_ERROR NUMBER;
  MENSAJE_ERROR VARCHAR2(2000); 
  sql_statement clob := empty_clob();
  sql_text ora_name_list_t;
  len binary_integer :=0;
pragma autonomous_transaction;  
BEGIN 
  INSERT INTO ERROR_LOG(id_error_log, codigo_error, mensaje_del_error, error_statement, sql_statement) 
      values(1,1,'', 1,  empty_clob());                                  
  commit;  
  for i in 1..ORA_SERVER_ERROR_DEPTH 
  loop  
      IF I = 1 THEN
        SELECT ERROR_STATEMENT_SEQ.NEXTVAL
        INTO ID_STATEMENT 
        FROM DUAL;
      END IF;
      
      dbms_lob.createtemporary(sql_statement, TRUE);
      dbms_lob.open(sql_statement, dbms_lob.lob_readwrite);  
      
      CODIGO_ERROR := ora_server_error(i);
      MENSAJE_ERROR := ORA_SERVER_ERROR_MSG(i);
      
      SELECT ERROR_LOG_SEQ.NEXTVAL 
      INTO ID_ERROR 
      FROM DUAL;
      
      for i in 1..ora_sql_txt(sql_text) 
      loop
        len := length(sql_text(i));
        dbms_lob.writeappend(sql_statement, len, sql_text(i));    
      end loop;
      dbms_lob.close(sql_statement);
          
      INSERT INTO ERROR_LOG(id_error_log, codigo_error, mensaje_del_error, error_statement, sql_statement) 
      VALUES( ID_ERROR, CODIGO_ERROR,MENSAJE_ERROR, id_statement, sql_statement);
  end loop;  
exception 
  when others then
      SELECT ERROR_STATEMENT_SEQ.NEXTVAL
      INTO ID_STATEMENT 
      FROM DUAL;
      
      SELECT ERROR_LOG_SEQ.NEXTVAL 
      INTO ID_ERROR 
      FROM DUAL;
      MENSAJE_ERROR := sqlerrm;
      CODIGO_ERROR := sqlcode;
      INSERT INTO ERROR_LOG(id_error_log, codigo_error, mensaje_del_error, error_statement, sql_statement) 
      values(id_error,CODIGO_ERROR,MENSAJE_ERROR, id_statement, empty_clob());                                  
      dbms_lob.close(sql_statement);  
END TRIG_ASERVERERROR;  


And this is the code I use to test that the trigger is being fired:
- A normal exception:
begin
  raise NO_DATA_FOUND;
end;

- A table not found exception
select * from TABLE_DOESNT_EXIST

Also if a check the next value of the ERROR_STATEMENT_SEQ sequence it doesn't change. Any more ideas? Is there a way to disable the firing of triggers?

Thanks, Joaquin

Re: Triggers fail to fire (merged 3) [message #383311 is a reply to message #383260] Wed, 28 January 2009 03:59 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, firstly, from the documentation, a No Data Found error doesn't make a SERVERERROR trigger fire.

Secondly - you've specified the trigger as 'ON SCHEMA'. Are you sure that you've compiled the trigger up as the same user that you're doing the testing with. Try making it an 'ON DATABASE' trigger, and test again.
Previous Topic: I am facing a problem during INSERT operation its giving Unique Constraint Error.
Next Topic: SQL query help
Goto Forum:
  


Current Time: Fri Dec 09 21:19:47 CST 2016

Total time taken to generate the page: 0.05668 seconds