Get the sql that triggers a trigger before update

Question

Get the sql that triggers a trigger before update

Navigation

#1 by (1 votes)

0

I have a very simple trigger before update, I would like to get the sql that triggers that trigger.

CREATE OR REPLACE TRIGGER atualiza_subelemento
BEFORE UPDATE ON tb_material
FOR EACH ROW
BEGIN
IF :OLD.CD_SUBELEMENTO IS NOT NULL THEN
DBMS_OUTPUT.PUT('sql atual',current_sql);
      RAISE_APPLICATION_ERROR (-20504,'Material não pode ser atualizado com codigo subelemento já existente');
END IF;
END;

Something like this "current_sql".

sql trigger

asked by anonymous 07.11.2018 / 14:51

1 answer

How to resolve error in printing strings? Unselect Radio Button of Angular Material

score 1 · Answer 1

Good people, the solution I found was to create a procedure "who called me" or who called and then create a function to execute it. And after that, I call inside the trigger. Below are the functions:

create or replace procedure quem_me_chamou( owner      out varchar2,
                     name       out varchar2,
                     lineno     out number,
                     caller_t   out varchar2 )
as
call_stack  varchar2(4096) default dbms_utility.format_call_stack;
n           number;
found_stack BOOLEAN default FALSE;
line        varchar2(255);
cnt         number := 0;
begin

loop
    n := instr( call_stack, chr(10) );
    exit when ( cnt = 3 or n is NULL or n = 0 );

    line := substr( call_stack, 1, n-1 );
    call_stack := substr( call_stack, n+1 );

    if ( NOT found_stack ) then
        if ( line like '%handle%number%name%' ) then
            found_stack := TRUE;
        end if;
    else
        cnt := cnt + 1;
        -- cnt = 1 is ME
        -- cnt = 2 is MY Caller
        -- cnt = 3 is Their Caller
        if ( cnt = 3 ) then
            lineno := to_number(substr( line, 13, 6 ));
            line   := substr( line, 21 );
            if ( line like 'pr%' ) then
                n := length( 'procedure ' );
            elsif ( line like 'fun%' ) then
                n := length( 'function ' );
            elsif ( line like 'package body%' ) then
                n := length( 'package body ' );
            elsif ( line like 'pack%' ) then
                n := length( 'package ' );
            elsif ( line like 'anonymous%' ) then
                n := length( 'anonymous block ' );
            else
                n := null;
            end if;
            if ( n is not null ) then
               caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
            else
               caller_t := 'TRIGGER';
            end if;

            line := substr( line, nvl(n,1) );
            n := instr( line, '.' );
            owner := ltrim(rtrim(substr( line, 1, n-1 )));
            name  := ltrim(rtrim(substr( line, n+1 )));
        end if;
    end if;
end loop;
end;

create or replace function quem_sou_eu return varchar2
is
l_owner        varchar2(30);
l_name      varchar2(30);
l_lineno    number;
l_type      varchar2(30);
begin
quem_me_chamou( l_owner, l_name, l_lineno, l_type );
return l_owner || '.' || l_name;
end;

And then I call the trigger

CREATE OR REPLACE TRIGGER atualiza_cod_subelemento
BEFORE UPDATE ON catalogo.tb_material_servico
FOR EACH ROW
BEGIN
IF :OLD.CD_SUBELEMENTO IS NOT NULL THEN
DBMS_OUTPUT.PUT(quem_sou_eu);
RAISE_APPLICATION_ERROR (-20504,'Material não pode ser atualizado com codigo subelemento já existente');
END IF;
END;