Oracle trigger: declare a global variable

Question

I have a table for which I wrote 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 update EMPLOYEE trigger desktop. But does the v_array array not store data? Can anybody help?

Answer 1

declare v_array in the package if you want to make it global (for 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-segment global variables, use relational tables (with corresponding multi-user locking).

Answer 2

How do you know that it does not save data? You declare an array inside the trigger. So this is not global, but local. Every time a trigger is triggered, you get a new array. You add one item, display its counter and release it again. The score is 1 , so it works.

Your code works fine, although it's useless. :) What did you mean? No waiting, the account belongs to another array. You put an element in a local array and show the counter of another (global?) Array. No wonder this will not work. I think you are modifying the wrong array.