Oracle trigger: Declare global variable

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

CREATE OR REPLACE TRIGGER EMPLOYEE_TRG 
AFTER INSERT OR DE开发者_JS百科LETE 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?

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).

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.