how to pass an argument from a procedure to trigger PL SQL in oracle-CodePudding

knowing that the argument(id_empl) I want to send is not stored in the database so I can't retrieve it with :NEW.id_empl or :OLD.id_empl

--my procedure
create or replace procedure delete_pdb(
id_pdb IN pdb.id%TYPE,
id_empl IN empl.id%TYPE)
IS
BEGIN
delete from pdb where id=id_pdb;
END delete_pdb;
/
--my trigger

create or replace trigger archive_pdb 
AFTER UPDATE OR DELETE on pdb
FOR EACH ROW
begin
--i want to pick it up here (id_empl)
end;

CodePudding user response：

You cannot.

You can get the id_pdb value using :OLD.id since that is a value in the deleted table but you cannot get the id_empl values since it is unrelated to the DELETE action.

db<>fiddle here

CodePudding user response：

The id_empl has nothing to do with the table pdb. It is only known to the function you are calling. Hence using it in a trigger where you deal with an action on the table seems to be the wrong place.

From what I see, it seems that you want to store the employee responsible for the action in the logging table. Usually one would simply store the current user instead, but in environments with a connection pool for instance, where all users share the same login account, this does not help.

One way to deal with this is to store the OSUSER instead:

SELECT SYS_CONTEXT('USERENV', 'OS_USER') FROM DUAL;

If this does not suffice and you must store the id_empl instead, you'll have to remember the id_empl in the session context and use it from there. One simple way to do this is to use a PL/SQL package with a public variable.

Package header

CREATE OR REPLACE PACKAGE pk_pdb_actions IS
  pkv_id_empl  empl.id%TYPE;
BEGIN
  PROCEDURE delete_pdb(p_id_pdb IN pdb.id%TYPE, p_id_empl IN empl.id%TYPE);
  ...
END pk_pdb_actions;

Package body

CREATE OR REPLACE PACKAGE BODY pk_pdb_actions IS
BEGIN
  PROCEDURE delete_pdb(p_id_pdb IN pdb.id%TYPE, p_id_empl IN empl.id%TYPE) IS
  BEGIN
    pkv_id_empl := p_id_empl;
    DELETE FROM pdb WHERE id = p_id_pdb;
  END delete_pdb;
  ...
END pk_pdb_actions;

Trigger

CREATE OR REPLACE trigger trg_archive_pdb 
AFTER UPDATE OR DELETE on pdb
FOR EACH ROW
BEGIN
  INSERT INTO archive_pdb (id_empl, id_pdb) 
    VALUES (pk_pdb_actions.pkv_id_empl, :old.id);
END trg_archive_pdb;