Oracle AFTER INSERT触发器 [英] Oracle AFTER INSERT Trigger

问题描述

以下触发器将不会触发.触发器在添加'SELECT c.deposit_id…代码段之前起作用.任何帮助将不胜感激.如果发现该表中的外键已链接到另一个表(TRANSACTION_TABLE)，则在对CASH_OR_CREDIT表进行插入后将触发该触发器.

The following trigger will not fire. The trigger worked before adding the 'SELECT c.deposit_id … piece of code. Any help will be greatly appreciated. The trigger is meant to fire after an insert is made on CASH_OR_CREDIT table if the foreign key in this table is found to be linked to another table (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;
   /`

推荐答案

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

Thank you for all of your advice and input. I solved the problem. The exception text revealed that the table mutates when you attempt to query it leading to the trigger failure. The trick to checking the validity of the child table to the parent table after an INSERT and allowing the trigger to fire is to remove the reference to the child (trigger) table and to perform the join using :NEW.foreign_key to join to the parent table. I learned a lot while trying to debug this :)

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');

这篇关于Oracle AFTER INSERT触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！