Menu de navegação

[JhonnyParticipante]

Boa tarde!!!

Preciso de uma ajuda, por favor. Ao INSERIR/INSERT ou ATUALIZAR/UPDATE um registro na tabela, quando a coluna PADRAO (existente na tabela) for definida com valor 1, ela deve setar o outro registro que estiver com valor 1 para 0 e deixar o registro novo inserido/atualizado com o valor 1, ou seja, nessa tabela somente um registro pode ser definido com valor 1 na coluna PADRAO. A Trigger abaixo funciona ao INSERIR/INSERT, mas não funciona ao ATUALIZAR/UPDATE
———-
CREATE OR REPLACE TRIGGER Alter_Column_Padrao
BEFORE INSERT OR UPDATE
ON NOME_TABELA
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (NEW.PADRAO = 1)
BEGIN
UPDATE NOME_TABELA SET PADRAO = 0;
END;

———-
Alguém sabe a solução?

[spernegaParticipante]

Jhonny,

A tabela desta trigger e a que está sofrendo o UPDATE são duas tabelas diferentes?

[JhonnyParticipante]

Não. É a mesma tabela. Eu vi que na questão de “tabela mutante”, certo? Mas, como posso fazer para fazer isso funcionar?

[spernegaParticipante]

Tenta assim:

CREATE OR REPLACE TRIGGER Alter_Column_Padrao
BEFORE INSERT OR UPDATE
ON NOME_TABELA
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
if :new.padrao = 1 then
:new.PADRAO := 0;
end if;
END;

Supondo que você quer modificar o campo de um para zero no registro que está sendo alterado.

[JhonnyParticipante]

Infelizmente não funcionou, nem o INSERT funcionou agora. 🙁
Alguma outra sugestão, por favor?

[spernegaParticipante]

Tenta colocar um tratamento de exception para ver o erro:

CREATE OR REPLACE TRIGGER Alter_Column_Padrao
BEFORE INSERT OR UPDATE
ON NOME_TABELA
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
if :new.padrao = 1 then
:new.PADRAO := 0;
end if;
exception
when others then
raise_application_error(-20200,sqlerrm(sqlcode));
END;

[spernegaParticipante]

ou tente desta forma:
Assim foge da trigger mutante

CREATE OR REPLACE TRIGGER Alter_Column_Padrao
BEFORE INSERT OR UPDATE
ON NOME_TABELA
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Declare
pragma autonomous_transaction;
BEGIN
if :new.padrao = 1 then
—
update NOME_TABELA set padrao = 0
where campo ou campos de PK :new.campo ou campos de PK
—
end if;
—
exception
when others then
raise_application_error(-20200,sqlerrm(sqlcode));
END;
/

[José Laurindo ChiappaModerador]

Para o colega que perguntou é o seguinte : Colega, tem muuuuita coisa errada aí… Primeiro de tudo, no corpo de uma trigger vc ** NÃO PODE ** fazer nem SELECT nem INSERT/UPDATE/DELETE na mesma tabela aonde a trigger tá amarrada, sob pena de erro de MUTATING TRIGGER… ok ?
Em segundo lugar, vc *** NÃO PODE ** , NUNCA, esquecer que o RDBMS Oracle é ** MULTI-USUÁRIO **, então há chance TOTAL de que dois usuários ao mesmo tempo queiram disparar a rotina que faz UPDATE ou INSERT na sua tabela – assim, vc TEM que SERIALIZAR o acesso, de modo que apenas uma execução da rotina possa ocorrer – normalmente isso implica em LOCKAR/BLOQUEAR a tabela enquanto o trigger tá disparando….
Esse tipo de FALHA (ie, programador que não pensa em modo multi-usuário, que não prevê Controles pro caso da rotina dele disparar múltiplas vezes ao mesmo tempo) é bem comum, pesquise em asktom.oracle.com por SERIALIZABLE que vc acha diversas refs…

Em terceiro lugar, se vc quer ter Ações diferentes para INSERT e para UPDATE numa trigger que pode ser disparada por qualquer um dos dois DMLs, vc DEVERIA prever isso no código da trigger , usando as variávels de sistema INSERTING e UPDATING …

Finalmente, nos explica a LÓGICA dessa tabela : é uma tabela que vai servir de flag ? Se sim, ao que entendo essa tabela só poderia ter no máximo 1 registro, que inicialmente TERIA que ser inserido com a coluna contendo ZERO , e depois desse único registro inserido NÂO Poderia mais ter INSERTs, só UPDATEs com a coluna recebendo 1…. É isso ?
Ou então vc quer ter no máximo dois registros na tabela, com o primeiro sendo validado que só recebeu zero no campo, aí o segundo registro a INSERIR só podendo conter 1 na coluna ? Se sim, novamente, QUAL É A NECESSIDADE disso, exatamente o que vc quer fazer ???

EXPLICA pra gente o que vc quer, como se a gente não soubesse NADA do que vc quer (pois REALMENTE NÂO SABEMOS!!), diga direitinho QUAL lógica que vc quer implementar, quais Validações vc quer que sejam feitas e de qual maneira E e nos dê o CREATE da tabela (incluindo PKs), que a gente te mostra algumas opções : além do Trigger (que PROVAVELMENTE iria demandar algum tipo de LOCK e talvez uma Outra tabela de controle que vai manter a contagem de registros e/ou a criação de uma nova sessão com AUTONOMOUS TRANSACTION) PROVAVELMENTE nós teremos Também a opção de criar constraint numa VIEW MATERIALIZADA…

[]s

Chiappa

[JhonnyParticipante]

Olá amigos!

Quero agradecer pela disposição de vocês em me ajudar, lendo a orientação de vocês e pesquisando acabei achando uma solução eficaz para o problema. Adicionei as 2 linhas abaixo, acima do “begin” e funcionou.

---

“DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;”

“The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction…”

Fonte: https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm

[José Laurindo ChiappaModerador]

Bom, eu ** tenho ** que te Avisar, até pra que depois vc não alegue ignorância : ao usar AUTONOMOUS TRANSACTION vc está abrindo uma Nova Transação, que será comitada / finalizada INDEPENDENTEMENTE da transação em curso quando o trigger disparou – assim, digamos que o usuário fez um INSERT, o trigger disparou, via AT ele manipulou os dados da tabela (** E ** por causa da AT essa manipulação FOI comitada implicitamente), aí o usuário manda um ROLLBACK : vc não acha que os dados vão ficar inconsistentes, parte desfeito e parte não ??? Não posso julgar, já que vc NÂO nos deu os esclarecimentos que pedi mas PENSO que questões do tipo, de INTEGRIDADE DE DADOS vão PULULAR com esse tipo de solução : é JUSTAMENTE o que é explicado em https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:290416059674 , https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2212445691154 e https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:469621337269 ….

Claro, nós estamos aqui Totalmente de Fora, sem saber o que vc quer implementar, mas que fique CRISTALINAMENTE CLARO que AT ** não ** é sem consequências, e DE MODO ALGUM ela é a “SOLUÇÂO” automática para mutating trigger que algumas pessoas Erradamente parecem pensar…

[]s

 Chiappa

[spernegaParticipante]

Boa tarde Chiappa,

Concordo com você, não temos informações suficientes do colega.
Com certeza existem outras soluções mais limpas para resolver o problema dele.

Só para constar, já tive que usar a solução do AUTONOMOUS TRANSACTION para uma um tratamento de interface do CIOT com webservice e foi a única solução que encontramos e funciona bem.

Mas de qualquer forma seria interessante o Jhonny detalhar melhor o problema dele.

[José Laurindo ChiappaModerador]

Sim : só quis deixar Claro lá pro colega que AT é uma Opção mas *** DESDE QUE *** a pessoa ENTENDA que basicamente está criando uma outra transação, assim ** NÂO HAVERÁ INTEGRIDADE DE DADOS NENHUMA ** entre as manipulações de dados ocorrendo , e que PORTANTO não se pode contar com o conceito de rollback, em caso de falha (por qual motivo for) na transação “primária” os dados updateados/inseridos na AT NÂO VÂO ser desfeitos… SE (e é um grande SE!!) o aplicativo do sujeito está contando com isso OK, AT vai funcionar corretamente mas não é o que a gente costuma ver por aí, não….

É por isso que muitos experts recomendam usar AT apenas e tão somente para LOGs (por exemplo, log de erros) pois (tipicamente) num caso desse não importa em absoluto se a transação principal funcionou ou não, o log em si tem que ser gravado e criado Independentemente…

[]s

Chiappa

[Autor]

Posts