Oracle AFTER INSERT Trigger

Question

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;
   /`

Answer 1

If it works without the SELECT c.deposit_id … piece then, presumably, that is what is causing an exception which is then being swallowed by the WHEN OTHERS exception handler being used and causing the trigger to look like it is not firing. You should be able to confirm that by checking whatever table/log schema.necessay_functions.debugger( is logging to.

What are the business rules around the l_dep_key value? Specifically, is it expected that the SELECT statement used to populate l_dep_key will always return a result (and only 1 result at that)? If so, at the very least wrap that statement with an anonymous block and explicitly handle any exceptions that conflict with those business rules.

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;

As OldProgrammer stated in a comment, the exception handling in your provided code has much room for improvement. Should you really be swallowing any and all exceptions that may be thrown by the code in this trigger?

Also, as a general tip, when logging exceptions instead of just logging SQLERRM use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() instead, as it gives you more context around the exception. Future you and/or future debuggers of this will thank you for it.

Answer 2

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