Log trigger that saves the numbering of the JOB that executed the change

Asked

Viewed 70 times

0

I need an oracle resource to use on a Trigger, which searches which JOB or Scheduler_job executed such a change. I am using Sys_context to get some example information: User, Terminal, Program Etc...

 -- Grava as informações de rastreio
  Select substr(sys_context('USERENV', 'CURRENT_USER'), 1, 250),
         substr(sys_context('USERENV', 'OS_USER'), 1, 250),
         substr(sys_context('USERENV', 'TERMINAL'), 1, 250),
         substr(sys_context('USERENV', 'MODULE'), 1, 250),
         substr(sys_context('USERENV', 'ACTION'), 1, 2000)

    Into vsuserbanco, vsusermaq, vsterminal, vsmodulo, vsaction
    From dual;
oracle pl-sql
  • 1

    It has two parameters BG_JOB_ID FG_JOB_ID I have no way to test now but seems to be a path. https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm

  • I got the required result using FG_JOB_ID

  • I couldn’t understand the difference between the lawsuits.

  • BG_JOB_ID search job in the background FG_JOB_ID search job in the foreground When the process is performed directly job, for example an Update in the Trigger table will save the FG_JOB_ID , if it is a precedent in JOB that performs a logic and finally executes an UPDATE it will write BG_JOB_ID

1 answer

0


CREATE OR REPLACE TRIGGER TL_MAP_PRODUTO_LOG
AFTER UPDATE ON PRODUTO
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW

DECLARE
  ------------------
  ---  VARIAVEIS ---
  Vsuserbanco VARCHAR2(250);
  Vsusermaq VARCHAR2(250);
  Vsterminal VARCHAR2(250);
  Vsmodulo VARCHAR2(250);
  Vsaction VARCHAR2(250);
  Vsuserapp VARCHAR2(250);
  V_OSUSER  VARCHAR2(50);
  V_DATA    DATE := SYSDATE;
  V_TABELA  VARCHAR2(40) := 'PRODUTO';
  V_CHAVE  VARCHAR2(20);
  V_CONTEUDO  VARCHAR2(4000);
  V_BG_JOB_ID VARCHAR2(4000);
  V_FG_JOB_ID VARCHAR2(4000);
  -----------------------------------------------------------------------------
BEGIN
  -----------------------------------------------------------------------------
  -----------------------------------------------------------------------------
--BUSCA INFORMAÇÕES GERAIS DO USUARIO
SELECT Substr(Sys_Context('USERENV', 'CURRENT_USER'), 1, 250),
       Substr(Sys_Context('USERENV', 'OS_USER'), 1, 250),
       Substr(Sys_Context('USERENV', 'TERMINAL'), 1, 250),
       Substr(Sys_Context('USERENV', 'MODULE'), 1, 250),
       Substr(Sys_Context('USERENV', 'ACTION'), 1, 250),
       Sys_Context('USERENV', 'BG_JOB_ID') Bg_Job_Id, --busca job em segundo plano
       Sys_Context('USERENV', 'FG_JOB_ID') Fg_Job_Id  -- busca job em primeiro plano
  INTO Vsuserbanco, Vsusermaq, Vsterminal, Vsmodulo, Vsaction,  V_BG_JOB_ID, V_FG_JOB_ID
  FROM Dual;

  -----------------------------------------------------------------------------
  -------> DESCRICAO 
  IF (NVL(:OLD.DESCRICAO,0) <> NVL(:NEW.DESCRICAO,0)) THEN
  V_CHAVE := :NEW.PRODUTO;
  V_CONTEUDO := 'A coluna DESCRICAO foi alterada de: '|| :OLD.DESCRICAO || ' para: ' || :NEW.DESCRICAO;
  INSERT INTO TABELA_LOG (Tabela, Chave, Data, Conteudo, Vsuserbanco, Vsusermaq, Vsterminal,   Vsmodulo, Vsaction, BG_JOB_ID,FG_JOB_ID )
                                    VALUES (V_TABELA,V_CHAVE,V_DATA,V_CONTEUDO, Vsuserbanco, Vsusermaq, Vsterminal, Vsmodulo, Vsaction,  V_BG_JOB_ID, V_FG_JOB_ID);
  END IF;
   -----------------------------------------------------------------------------
END;

Browser other questions tagged oracle pl-sql

You are not signed in. Login or sign up in order to post.