避免使用Trigger和raise_application_error将数据插入表中，而是将数据存储到Log表中

Question

我需要检查产品销售是否过期，将其日期与计算机的实际日期进行比较。如果产品过期，那么我不能将产品数据插入订单表，但我需要将它们插入到日志表中。我可以避免使用raise_application_error (-20001,'This product is not for sale anymore');插入到Order Table中。但是，当我使用raise_application_error时，似乎raise_application_error回滚也命令INSERT INTO LogTable ...，这是在触发器内。是否有更好的方法来解决这个问题不使用raise_application_error?观察:我需要使用Trigger而不能使用INSTEAD-OF

create or replace TRIGGER Insercao_DetalhesPedido
    AFTER INSERT ON DetalhesPedido
    REFERENCING new AS novo_DP
    FOR EACH ROW
  DECLARE
    dataPedido Pedido.DTPedido%TYPE;
    dataVenda Produto.DTFimVenda%TYPE;
    dtVenda_Expirou EXCEPTION;
  BEGIN
    -- get the date from the order(it is a SYSDATE)
    SELECT DTPedido INTO dataPedido
    FROM Pedido 
    WHERE Pedido.codigo = :novo_DP.codigoPedido;
    -- get the date from the product(when it will expire)
    SELECT DTFimVenda INTO dataVenda
    FROM Produto
    WHERE Produto.codigo = :novo_DP.codigoProduto;
    --has the product expired?
    IF dataVenda < dataPedido THEN 
    -- Insert INTO the log table saying that this product have expired
      INSERT INTO Log
      VALUES(:novo_DP.codigoPedido, :novo_DP.codigoProduto, cast(SYSDATE as timestamp), dataPedido, 
             'Insercao Falha. DTVenda('||dataVenda||') < DataPedido('||dataPedido||')');
      RAISE dtVenda_Expirou;
    ELSE
      -- Insert INTO the log table saying that the product was succesfully inserted
       INSERT INTO Log
       VALUES(:novo_DP.codigoPedido, :novo_DP.codigoProduto, cast(SYSDATE as timestamp), dataPedido, 
             'Inserção De Produto em DetalhesPedido com sucesso');
    END IF;  
  EXCEPTION
  -- Product expired
  WHEN dtVenda_Expirou THEN
      raise_application_error (-20001,'A DATA DE VENDA DO PRODUTO EXPIROU');
  -- Order number not found
  WHEN NO_DATA_FOUND THEN
    raise_application_error (-20001,'NUMERO DO PEDIDO NÃO EXISTE');
END Insercao_DetalhesPedido;

我放了一些英文注释，因为我的代码是葡萄牙语的

Answer 1

您可以使用Autonomous Pragma来记录您的数据，而不管异常。基本上，您创建一个过程来插入到日志表中。这个过程的语法可以是:

CREATE OR REPLACE PROCEDURE auto_log(your parameters) is
....
PRAGMA AUTONOMOUS TRANSACTION;   --This statement makes the procedure autonomous
BEGIN
--your insert into log statement
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
END;
/

你的触发器会是这样的:

....
--has the product expired?
IF dataVenda < dataPedido THEN 
-- Insert INTO the log table saying that this product have expired
   auto_log(your parameters)  --- call the procedure
   RAISE dtVenda_Expirou;
ELSE
  -- Insert INTO the log table saying that the product was succesfully inserted
  auto_log(your parameters)  --- call the procedure
END IF;  
EXCEPTION
-- Product expired
WHEN dtVenda_Expirou THEN
    raise_application_error (-20001,'A DATA DE VENDA DO PRODUTO EXPIROU');
-- Order number not found
WHEN NO_DATA_FOUND THEN
  raise_application_error (-20001,'NUMERO DO PEDIDO NÃO EXISTE');
END Insercao_DetalhesPedido;

看到

Answer 2

这里需要使用Autonomous Pragma。它将确保父事务不会回滚子事务。这是记录sql和pl/sql的标准做法。

请看下面的例子:http://www.java2s.com/Tutorial/Oracle/0560__Trigger/Autonomoustriggers.htm

编辑:

我的建议是创建一个单独的pl/sql进程，使用自治pragma插入日志表，并在需要时调用该进程。这将确保日志语句不会回滚。