oracle - 如何捕获触发器内部的错误

Question

我想创建一个触发器，它应该捕获触发器内部发生的所有错误。它应该存储在一些日志表中。这是我的挑战how to capture the error occur in DML statments。在日志中捕获的 DML 语句错误respective table name along with column name and error message。它还应该使用确切的行号捕获所有其他错误，例如 no_data_found。

请看我下面的代码。如果有任何更改需要告诉我们。

create or replace TRIGGER user_name.sample_trg 
AFTER UPDATE ON user_name.transaction_tb
FOR EACH ROW
DECLARE
 variable_ln  number;
 l_err   varcha2(4000);
  
BEGIN
    select column_value
    into variable_ln
    from tb1
    where colum_1 = :NEW.colum_1
    

    IF UPDATING THEN

      INSERT
      INTO hisotry_tb
        (
          column1,
          column2,
          column3,
          column4,
        )
        VALUES
        (
          :NEW.column1,
          :NEW.column2,
          :NEW.column3,
          :NEW.column4,
        );
        
     END IF; 

IF INSERTING THEN

      INSERT
      INTO hisotry_tb
        (
          column5,
          column6,
          column7,
          column8,
        )
        VALUES
        (
          :NEW.column5,
          :NEW.column6,
          :NEW.column7,
          :NEW.column8,
        );
        
     END IF;     
        
EXCEPTION
  WHEN OTHERS THEN
    l_err := DBMS_UTILITY.FORMAT_ERROR_STACK || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
    INSERT INTO log (erro_msg, trigger_name, column_name) 
    VALUES (l_err, 'sample_trg', '?');
    
      DBMS_OUTPUT.put_line (l_err);
  
END;

标签： oracleexceptionplsqlerror-handlingtriggers