Refactor|Unused Identifiers in Compound Triggers

Toad for Oracle & DevOps Toolkit
1

The Unused Identifiers seems to be flagging every vairiable as unused now that I had to change an old before insert or update trigger into a compound trigger. It isn't looking in the BEFORE/AFTER EACH ROW IS and BEFORE/AFTER STATEMENT IS blocks in my case.

2

Can you show example of your claim?

3 
CREATE OR REPLACE TRIGGER y.zz
   FOR INSERT OR UPDATE
   ON y.abcd
   COMPOUND TRIGGER
   lnum_system_implem_fy_id   system_activity_ref.system_implem_fy_id%TYPE;
   lnum_system_decom_fy_id    system_activity_ref.system_decom_fy_id%TYPE;
   lvar_bee_bear              jnlu_ref.jnlu_id%TYPE;
   lnum_bee_bear_issue        PLS_INTEGER := 0;
   lnum_user_sa_id            abcd.created_user_sa_id%TYPE
                                 := SYS_CONTEXT('APP_USER_CTX', 'USER');
   ldat_cd                    abcd.r1crdte%TYPE := CURRENT_DATE;
   ldat_ct                    abcd.bee_bear_date_tz%TYPE := CURRENT_TIMESTAMP;
   lboo_add_new               BOOLEAN := FALSE;
   TYPE ltyp_coar IS RECORD
   (
      activity_sa_id    abcd.activity_sa_id%TYPE,
      fiscal_year_id    abcd.fiscal_year_id%TYPE,
      wkccd             abcd.wkccd%TYPE,
      cowke             abcd.cowke%TYPE
   );
   TYPE ltab_coar IS TABLE OF ltyp_coar;
   -- Declare a collection to hold row data
   lcol_coar                  ltab_coar := ltab_coar();
   -- Before statement section
   BEFORE STATEMENT
   IS
   BEGIN
      lcol_coar.delete;
   END BEFORE STATEMENT;
   BEFORE EACH ROW
   IS
   BEGIN
      SELECT system_implem_fy_id, system_decom_fy_id
        INTO lnum_system_implem_fy_id, lnum_system_decom_fy_id
        FROM system_activity_ref;
   END BEFORE EACH ROW;
   AFTER EACH ROW
   IS
   BEGIN
      IF lboo_add_new
      THEN
         lcol_coar.EXTEND;
         lcol_coar(lcol_coar.LAST) := ltyp_coar(:new.activity_sa_id);
      END IF;
   END AFTER EACH ROW;
  -- After statement section
   AFTER STATEMENT
   IS
   BEGIN
      FOR i IN 1 .. lcol_coar.COUNT
      LOOP
         SELECT MIN(n.jnlu_id)
           INTO lvar_bee_bear
           FROM jnlu_ref n;
      END LOOP;
   END AFTER STATEMENT;
END zz;
/

Refactor results (line numbers represent pre-blank line removal):

[Variable] lnum_system_implem_fy_id (5: 4)
[Variable] lnum_system_decom_fy_id (6: 4)
[Variable] lvar_bee_bear (7: 4)
[Variable] lnum_bee_bear_issue (8: 4)
[Variable] lnum_user_sa_id (9: 4)
[Variable] ldat_cd (11: 4)
[Variable] ldat_ct (12: 4)
[Variable] lboo_add_new (14: 4)
[Variable] lcol_coar (27: 4)

4

Thanks, I will log this.