Oracle trigger: Declare global variable

Question

I have a table for which i have written a trigger:

CREATE OR REPLACE TRIGGER EMPLOYEE_TRG 
AFTER INSERT OR DELETE OR UPDATE ON EMPLOYEE 

FOR EACH ROW

DECLARE
  TYPE arr IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
  v_array arr;

BEGIN
  IF UPDATING THEN
    DBMS_OUTPUT.PUT_LINE('NEW DATA: ' || :new.NAME || ', OLD DATA: ' || :old.NAME);
    DBMS_OUTPUT.PUT_LINE('ID: ' || :new.P_ID);
    v_array(:new.P_ID) := :new.NAME;
    DBMS_OUTPUT.PUT_LINE('COUNTER: ' || v_array.COUNT); -- DISPLAY COUNTER: 1
  END IF; 
END;

when i'm updating EMPLOYEE table trigger's working fine. But v_array array isn't store data? could anybody please help?

v_date_array or v_array ?

J-16 SDiZ
– J-16 SDiZ

2011-02-17 06:24:40 +00:00
Commented Feb 17, 2011 at 6:24 — J-16 SDiZ
– J-16 SDiZ, Commented Feb 17, 2011 at 6:24

Vincent Malgrat · Accepted Answer · 2011-02-17 07:01:42Z

7

declare v_array in a package if you want to make it global (to a session -- each session will have its own copy of the variable).

CREATE OR REPLACE PACKAGE my_global_pkg IS

   TYPE arr IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
   g_array arr;

END my_global_pkg;

CREATE OR REPLACE TRIGGER EMPLOYEE_TRG 
   AFTER INSERT OR DELETE OR UPDATE ON EMPLOYEE 
   FOR EACH ROW
BEGIN
   IF UPDATING THEN
      DBMS_OUTPUT.PUT_LINE('NEW DATA: ' ||:new.NAME ||', OLD DATA: '||:old.NAME);
      DBMS_OUTPUT.PUT_LINE('ID: ' || :new.P_ID);
      my_global_pkg.g_array(:new.P_ID) := :new.NAME;
      DBMS_OUTPUT.PUT_LINE('COUNTER: ' || my_global_pkg.g_array.COUNT);
   END IF;
END;

For Multi-session global variables, use relational tables (with appropriate multi-user locking).

edited Feb 17, 2011 at 7:01

answered Feb 17, 2011 at 6:55

Vincent Malgrat

68k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GolezTrol · Accepted Answer · 2011-02-17 06:31:41Z

0

How do you know it doesn't save data? You declare the array inside the trigger. So it isn't global, but local. Everytime your trigger runs, you get a new array. You add one item, display its count, and release it again. The count shows 1, so that works.

~~Your code is working fine, although it's useless. :) What did you mean it to do?~~ No wait, the count belongs to a different array. You put an item in a local array, and display the count of another (global?) array. No wonder it won't work. I think you're modifying the wrong array.

answered Feb 17, 2011 at 6:31

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Collectives™ on Stack Overflow

Oracle trigger: Declare global variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related