Trigger using Autonomous_Transaction

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Wed, 14 Dec 2011 3:36 pm

Hello people, first of all, notice that I am new in PL SQL, so I'm still not crawling.
I took a test here in the company where I am who asked me to solve the following problem, a trigger.
This trigger, triggers when I delete a row information.
This problem, I'vê managed to solve via view using INSTEAD OF.
I managed to delete the line smoothly.
What happens now is that my superior said that there are still 3 ways to solve it.
The first would be that route view.
The second, I googled and found a function called PRAGMA AUTONOMOUS_TRANSACTION.

Seen in various forums that it depends what I'm going to use, and relies heavily on, best to use otherwise.

Anyway, it's a test I'm using here and would like to see what actually happens and what really makes this command.

My trigger is as follows:
Code: Select all
CREATE OR REPLACE TRIGGER rcf_trg_exc_lin_decl
AFTER DELETE
ON imp_declaracoes_lin
FOR EACH ROW
DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

  CURSOR cur_linha_decl (pn_declaracao_lin_id
  imp_declaracoes_lin.declaracao_lin_id%TYPE) IS
      SELECT qtde 
      FROM imp_declaracoes_lin
      WHERE declaracao_lin_id = pn_declaracao_lin_id;

  vn_tp_declaracao_id   imp_declaracoes.tp_declaracao_id%TYPE;
  vc_dsi                imp_declaracoes.dsi%TYPE;
  vn_antigo_consumo_id  rcf_consumos.consumo_id%TYPE;
  vn_novo_consumo_id    rcf_consumos.consumo_id%TYPE;
  vn_qtde               imp_declaracoes_lin.qtde%TYPE;

BEGIN
  IF (cmx_fnc_profile ('xxx') = 'S') OR (cmx_fnc_profile (yyy') = 'S' ) THEN
    SELECT tp_declaracao_id,
           dsi
    INTO   vn_tp_declaracao_id,
           vc_dsi
    FROM   imp_declaracoes
    WHERE  declaracao_id = :old.declaracao_id;

    IF (cmx_pkg_tabelas.codigo (vn_tp_declaracao_id) IN ('16','17')) AND (vc_dsi = 'N') THEN
      BEGIN
          OPEN cur_linha_decl(:old.declaracao_lin_id);
          FETCH cur_linha_decl
          INTO vn_qtde;
          CLOSE cur_linha_decl;

          SELECT antigo.consumo_id,
                 novo.consumo_id
          INTO   vn_antigo_consumo_id,
                 vn_novo_consumo_id
          FROM   rcf_consumos antigo,
                 rcf_consumos novo
          WHERE  antigo.nr_referencia        = :old.declaracao_id
          AND    novo.estoque_movimento_id   = antigo.estoque_movimento_id
          AND    novo.saida_movimento_id     = antigo.saida_movimento_id
          AND    novo.nr_referencia          IS NULL;

          UPDATE rcf_lancamentos
          SET consumo_id = vn_novo_consumo_id
          WHERE consumo_id = vn_antigo_consumo_id;

          DELETE FROM rcf_consumos
          WHERE consumo_id = vn_antigo_consumo_id;

          UPDATE rcf_consumos
          SET quantidade = quantidade+vn_qtde
          WHERE consumo_id = vn_novo_consumo_id;

       END;
     END IF;
   END IF;
COMMIT;   
END rcf_trg_exc_lin_decl;
except for the lines that are the PRAGMA and the COMMIT, is to view original, which gives error when the vn_tp_declaracao_id and the vc_dsi = S.

As not all lines pass through this part and even less by xxx and yyy profiles, so most lines are deleted normally, but some are locked because they were using these profiles and end up entering the cursor.

When I try to delete these lines, gives me error no data found (ORA-01403: no data found), but the line is there on the table.

My doubts: 1) is right the way to put the AUTONOMOUS_TRANSACTION PRAGMA?
2) That I'll find out after you put in the right place but, this PRAGMA solves the problem?

Thanks for your help, Freidinger
Freidinger

Poston Fri, 20 Jan 2012 3:57 pm

Your problem is that you are opening a course in the same table that you created the trigger, so you need to use the Autonomous.

I do not know another way to open cursor on the same table that the trigger without using the autonomous.

If someone has the solution put there!!
schnu
Location: Dongguan - Guangdong - China

Deus criou os loucos para confundir os sábios

Poston Fri, 03 Feb 2012 12:18 pm

1) is right the way to put the AUTONOMOUS_TRANSACTION PRAGMA?
2) That I'll find out after you put in the right place but, this PRAGMA solves the problem?

The use of PRAGMA AUTONOMOUS_T ... creates a new session in oracle and executes the code within the new session. It's kind of dangerous, so I'll put some links here for you to see some examples that use it don't works: viewtopic.php?t=96 viewtopic.php?t=1537 I particularly, this PRAGMA usso to create LOGS in other tables and committing, without affecting the current transaction , that is, the log will be recorded even if the original program der ROLLBACK.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • See also
    Replies
    Views
    Last Post


    Return to PL/SQL

    Who is online

    Users browsing this forum: Google Adsense [Bot] and 17 guests