Oracle AFTER INSERT Trigger

Question

以下触发器不会触发。触发器在添加"SELECT c.deposit_id…"代码之前工作。任何帮助都将不胜感激。如果发现CASH_OR_CREDIT表中的外键链接到另一个表（TRANSACTION_table），则触发器将在对该表进行插入后激发。

`
 CREATE OR REPLACE TRIGGER SEND_MONEY
 AFTER INSERT
 ON cash_or_credit
 REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
DECLARE
 system_header_info           NUMBER := 0;
 l_dep_key                     NUMBER := 0;

 CURSOR cur (cover_id NUMBER)
 IS
  SELECT header_id
    FROM headers
   WHERE party_site_id = cover_id;

  system_header_info   VARCHAR2 (10)
        := schema.necessay_functions.get_system_id ('DEPOSITS');

 BEGIN
 fnd_profile.put ('company_debugger', 'Y');
 schema.necessay_functions.debugger ('old.deposit_id =' || :OLD.deposit_id);
  schema.necessay_functions.debugger ('new.deposit_id =' || :NEW.deposit_id);
   OPEN cur (system_header_info);
  system_header_info := 0;
  FETCH cur1 INTO system_header_info;
  CLOSE cur1;
  schema.necessay_functions.debugger (
    'super_user.user_id =' || super_user.user_id);
   schema.necessay_functions.debugger (
    schema.necessay_functions.obtain_user_id (
     schema.necessay_functions.get_system_id ('DEPOSITS')));
  SELECT c.deposit_id
   INTO l_dep_key
  FROM schema.transaction_table o,
      schema.linker_table r,
      schema.cash_or_credit c
  WHERE     o.primary_key = r.primary_key
      AND o.table_name = 'INDIVIDUAL_REC'
      AND o.system_id = '265226'
      AND o.status = 'A'
      AND r.status = 'A'
      AND c.foreign_key = r.primary_key
      AND c.deposit_id = :NEW.deposit_id
      AND r.relationship_code IN ('EMPLOYER_OF');

    IF     super_user.user_id =
         schema.necessay_functions.obtain_user_id (
            schema.necessay_functions.get_system_id ('DEPOSITS'))
  AND l_dep_key = :NEW.deposit_id
  THEN
  schema.necessay_functions.debugger ('Inside If Condition');
  FOR sys_comp
     IN (SELECT *
           FROM schema.transaction_table
          WHERE     status = 'A'
                AND table_name = 'DEPOSITS'
                AND primary_key = :NEW.deposit_id
                AND system_id =
                       schema.necessay_functions.get_system_id (
                          'DEPOSITS'))
  LOOP
     schema.necessay_functions.debugger ('Inside Loop');
     schema.necessay_functions.send_xml_message ('SEND_SYSTEM_MSG',
                                                 'SEND.UPDATE',
                                                 system_header_info,
                                                 sys_comp.system_id,
                                                 sys_comp.system_key);
         END LOOP;
     ELSE
        schema.necessay_functions.send_xml_message ('SEND_SYSTEM_MSG',
                                              'SEND.CREATE',
                                              system_header_info,
                                              system_header_id,
                                              :NEW.deposit_id);
    END IF;
   EXCEPTION
    WHEN OTHERS
     THEN
  schema.necessay_functions.debugger ('Sqlerrm:' || SQLERRM);
 END SEND_MONEY;
   /`

Answer 1

如果它在没有SELECT c.deposit_id …块的情况下工作，那么，据推测，这就是导致异常的原因，该异常随后被正在使用的WHEN OTHERS异常处理程序吞噬，并导致触发器看起来像没有启动。您应该能够通过检查schema.necessay_functions.debugger(正在登录的任何表/日志来确认这一点。

l_dep_key值的业务规则是什么？具体来说，是否期望用于填充l_dep_key的SELECT语句始终返回一个结果（并且只有一个结果）？如果是这样的话，至少用匿名块包装该语句，并显式处理与这些业务规则冲突的任何异常。

BEGIN
    SELECT c.deposit_id
     INTO l_dep_key
    FROM schema.transaction_table o,
         schema.linker_table r,
         schema.cash_or_credit c
    WHERE     o.primary_key = r.primary_key
        AND o.table_name = 'INDIVIDUAL_REC'
        AND o.system_id = '265226'
        AND o.status = 'A'
        AND r.status = 'A'
        AND c.foreign_key = r.primary_key
        AND c.deposit_id = :NEW.deposit_id
        AND r.relationship_code IN ('EMPLOYER_OF');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        ...TAKE APPROPRIATE ACTION HERE...
        ...POSSIBLY LOG AND RAISE...
    WHEN TOO_MANY_ROWS THEN
        ...TAKE APPROPRIATE ACTION HERE...
        ...POSSIBLY LOG AND RAISE...
END;

正如OldProgrammer在评论中所说，您提供的代码中的异常处理还有很大的改进空间。您真的应该吞下这个触发器中的代码可能引发的任何和所有异常吗？

此外，作为一般提示，当记录异常而不仅仅记录SQLERRM时，请使用DBMS_UTILITY.FORMAT_ERROR_BACKTRACE（），因为它为您提供了更多有关异常的上下文。未来的你和/或未来的调试人员会为此感谢你。

Answer 2

感谢您的所有建议和意见。我解决了这个问题。异常文本显示，当您尝试查询该表时，该表会发生变化，从而导致触发器失败。在INSERT之后检查子表对父表的有效性并允许触发器触发的技巧是删除对子表（触发器）的引用，并使用：NEW.foreign_key执行联接以联接到父表。我在尝试调试时学到了很多：）

BEGIN
    SELECT COUNT(1)
    INTO l_dep_key
   FROM schema.transaction_table o,
     schema.linker_table r
  WHERE     o.primary_key = r.primary_key
    AND o.table_name = 'INDIVIDUAL_REC'
    AND o.system_id = '265226'
    AND o.status = 'A'
    AND r.status = 'A'
    AND o.foreign_key = r.primary_key
    AND r.primary_key = :NEW.foreign_key
    AND r.relationship_code IN ('EMPLOYER_OF');