Oi, Giovanni : então, como eu disse nós, mais experientes em tecnologia Oracle,
é que temos que orientar o cliente, faz parte do nosso trabalho, jamais aceitar
o que eles dizem sem questionamento...
No caso específico, eles estão falando ** BOBAGEM **, pura e simples :
primeiro, o que ocorre é que NÃO É qquer DDL que invalida a trigger (apenas os
que adicionam/diminuem/removem colunas)- pra cobrir o caso de coluna removida
da tabela-origem vc simplesmente usa INSERT da trigger a sintaxe :
INSERT INTO tabelaaudit VALUES (:old.coluna1, :old.coluna2..., :old.colunaN),
que aí se removerem colunas o trigger fica inválido, e quando uma trigger de
DML fica inválida, o DML (o DELETE no caso) vai ** BOMBAR **, vai ABORTAR, vai
dar pau, com uma mensagem BEM clara de trigger invalid - Não tem Como eles
esquecerem, pois o DELETE não funcionará, a aplicação vai dar pau...
Absolutamente não é o caso de que a auditoria vai falhar, se a trigger ficou
inválida é a própria aplicação que vai parar, penso eu que não tem como vc
"esquecer" , certo ?
Já no caso de adicionarem uma coluna na tabela-origem , pra começo de conversa
num ambiente regrado e Organizado alterações de modelo deveriam ser RARAS e
sempre feitas por um DBA, mas se mesmo assim for desejado/considerado útil, foi
JUSTAMENTE pra situações como essa que se criou no RDBMS Oracle a trigger de
DDL, que dispara quando um DDL é efetuado : nessa trigger vc tanto poderia
disparar um job que adiciona a nova coluna na tabela de audit, quanto (o que
seria mais correto, acho eu) poderia na trigger de DDL feito na tabela-origem
comparar a estrutura dela (na DBA_TABLEs, USER_TABLES, o que for) contra a
estrutura da outra tabela , abortando com RAISE_APPLICATION_ERROR se antes de
fazer a inclusão na tabela-origem o cara não incluiu na tabela de audit...
Essas verificações de DDL (que, como eu disse, num ambiente Organizado são
redundantes, não é qquer zémané que pode fazer DDL) tornariam o código
necessário pra usar triggers um pouquinho mais complexo, mas nada do outro
mundo, é uma meia dúzia de linhas, tipo assim, e IMHO ainda seria preferível,
pois manteria-se as vantagens da trigger (ie, de auditar QUALQUER delete na
tabela, não só os vindos da sua procedure, de ser transacional, etc, etc, veja
tudo que foi dito no e-mail anterior....
===> CASO realmente o cliente insista mesmo na solução de código dentro da sua
procedure, UMA VEZ que vc esteja Absolutamente, Totalmente, Positivamente CERTO
de que ele entendeu as desvantagens E os riscos do código procedural, aí sim vc
faria as alterações indicadas lá pelo Paulo (savepoints), E faria o tratamento
de erros , via EXCEPTIOn, tal como eu indiquei...
[]s
Chiappa
--- Em oracle_br@yahoogrupos.com.br, "Giovanni Ferreira de Sousa"
<giovanni.sousa@...> escreveu
>
> Obrigado pela explanação Paulo e Chiapa,
>
> Chiapa,
> Quando o cliente me solicitou isso, eu sugeri o uso de trigger como vc acabou
> de dizer, mas eles disseram que não usam TRIGGERS porque se houver algum DDL
> na tabela onde a TRIGGER é disparada invalida a TRIGGER e que eles não
> lembrariam de atualizar a TRIGGER e a auditoria seria falha. Sendo assim,
> estou fazendo conforme eles solicitaram.
> Qual a sua melhor sugestão para este caso?
> Segue a PROCEDURE que sugeri.
> A aplicação se encarrega de passar os parâmetros...
>
> CREATE OR REPLACE PROCEDURE DBSISMAC.sp_solicitacao_remanej_final (
> estado IN NUMBER,
> usuario IN NUMBER,
> competencia IN NUMBER,
> DS_USERNAME IN VARCHAR2)
> IS
> BEGIN
> INSERT INTO DBSISMAC.TL_TMQUADRO_01
> SELECT competencia AS NU_COMPETENCIA_SOLICITACAO,
> CO_MUNICIPIO_IBGE,
> VL_POP_PROPRIA_AMB,
> VL_POP_REFERENCIA_AMB,
> VL_POP_PROPRIA_HOSP,
> VL_POP_REFERENCIA_HOSP,
> VL_INCENTIVO_PERMANENTE,
> VL_ENC_OUTRA_UF,
> VL_REC_OUTRA_UF,
> VL_AJUSTE,
> VL_TOTAL_PPI_ASSISTENCIAL,
> CO_PROTOCOLO,
> CO_USUARIO,
> DT_ALTERACAO,
> DS_USERNAME AS DS_USERNAME,
> 'EXCLUÍDO' AS DS_OPERACAO,
> SYSDATE AS DT_OPERACAO
> FROM DBSISMAC.TM_QUADRO_01
> WHERE SUBSTR (CO_MUNICIPIO_IBGE, 0, 2) = estado;
>
> DELETE FROM DBSISMAC.TM_QUADRO_01
> WHERE SUBSTR (CO_MUNICIPIO_IBGE, 0, 2) = estado;
>
> INSERT INTO DBSISMAC.TL_TMQUADRO_02
> SELECT CO_MUNICIPIO_IBGE,
> NO_ESPECIFICACAO,
> VL_TOTAL_PPI_ASSISTENCIAL,
> CO_PROTOCOLO,
> competencia AS NU_COMPETENCIA_SOLICITACAO,
> CO_USUARIO,
> DT_ALTERACAO,
> DS_USERNAME AS DS_USERNAME,
> 'EXCLUÍDO' AS DS_OPERACAO,
> SYSDATE AS DT_OPERACAO
> FROM DBSISMAC.TM_QUADRO_02
> WHERE SUBSTR (CO_MUNICIPIO_IBGE, 0, 2) = estado;
>
> DELETE FROM DBSISMAC.TM_QUADRO_02
> WHERE SUBSTR (CO_MUNICIPIO_IBGE, 0, 2) = estado;
>
> INSERT INTO DBSISMAC.TL_TMQUADRO_03
> SELECT competencia AS NU_COMPETENCIA_SOLICITACAO,
> CO_MUNICIPIO_IBGE_ORIG,
> CO_MUNICIPIO_IBGE_EXEC,
> VL_GEST_ESTADUAL,
> VL_GEST_MUNICIPAL,
> VL_TOTAL_PPI_ASSISTENCIAL,
> CO_PROTOCOLO,
> CO_USUARIO,
> DT_ALTERACAO,
> DS_USERNAME AS DS_USERNAME,
> 'EXCLUÍDO' AS DS_OPERACAO,
> SYSDATE AS DT_OPERACAO
> FROM DBSISMAC.TM_QUADRO_03
> WHERE SUBSTR (CO_MUNICIPIO_IBGE_ORIG, 0, 2) = estado;
>
> DELETE FROM DBSISMAC.TM_QUADRO_03
> WHERE SUBSTR (CO_MUNICIPIO_IBGE_ORIG, 0, 2) = estado;
>
> INSERT INTO DBSISMAC.TL_TMQUADRO_04
> SELECT competencia AS NU_COMPETENCIA_SOLICITACAO,
> CO_MUNICIPIO_IBGE,
> CO_CNES,
> NO_UNIDADE,
> NU_TERMO,
> DT_PUBLIC_EXT_TERMO,
> CO_FUNDO,
> VL_TOTAL_FS,
> CO_PROTOCOLO,
> CO_USUARIO,
> DT_ALTERACAO,
> DS_USERNAME AS DS_USERNAME,
> 'EXCLUÍDO' AS DS_OPERACAO,
> SYSDATE AS DT_OPERACAO
> FROM DBSISMAC.TM_QUADRO_04
> WHERE SUBSTR (CO_MUNICIPIO_IBGE, 0, 2) = estado;
>
> DELETE FROM DBSISMAC.TM_QUADRO_04
> WHERE SUBSTR (CO_MUNICIPIO_IBGE, 0, 2) = estado;
>
> INSERT INTO DBSISMAC.TL_TMQUADRO_05
> SELECT competencia AS NU_COMPETENCIA_SOLICITACAO,
> CO_MUNICIPIO_IBGE,
> VL_TOTAL_FES,
> CO_PROTOCOLO,
> CO_USUARIO,
> DT_ALTERACAO,
> DS_USERNAME AS DS_USERNAME,
> 'EXCLUÍDO' AS DS_OPERACAO,
> SYSDATE AS DT_OPERACAO
> FROM DBSISMAC.TM_QUADRO_05
> WHERE SUBSTR (CO_MUNICIPIO_IBGE, 0, 2) = estado;
>
> DELETE FROM DBSISMAC.TM_QUADRO_05
> WHERE SUBSTR (CO_MUNICIPIO_IBGE, 0, 2) = estado;
>
> INSERT INTO DBSISMAC.TL_TMQUADRO_06
> SELECT competencia AS NU_COMPETENCIA_SOLICITACAO,
> CO_GESTAO,
> CO_MUNICIPIO_IBGE,
> CO_CNES,
> NO_UNIDADE,
> NU_CONTRATO,
> DT_PUBLIC_EXT_CONTRATO,
> VL_TOTAL_FNS,
> CO_PROTOCOLO,
> CO_USUARIO,
> DT_ALTERACAO,
> DS_USERNAME AS DS_USERNAME,
> 'EXCLUÍDO' AS DS_OPERACAO,
> SYSDATE AS DT_OPERACAO
> FROM DBSISMAC.TM_QUADRO_06
> WHERE SUBSTR (CO_MUNICIPIO_IBGE, 0, 2) = estado;
> DELETE FROM DBSISMAC.TM_QUADRO_06
> WHERE SUBSTR (CO_MUNICIPIO_IBGE, 0, 2) = estado;
> commit;
>
> exception when others then
> rollback;
> END;
> /
>
>
> Giovanni
>
>
> ________________________________
>
> De: oracle_br@yahoogrupos.com.br em nome de José Laurindo
> Enviada: qui 29/12/2011 11:06
> Para: oracle_br@yahoogrupos.com.br
> Assunto: [oracle_br] Re: Tratamento de logs
>
>
>
>
> Paulo, é absolutamente verdade que a maneira de desfazer apenas parte dos
> comandos da transação é o SAVEPOINT - no caso em questão, porém, uma solução
> procedural, além de ser mais longa, só vai ser disparada (óbvio) quando da
> execução do PL/SQL, se outras rotinas/programas fizerem DELETEs na tabela
> esses DELETEs passam SEM LOGS, e uma Auditoria em princípio deve pegar TODOS
> os DMLs, independente de onde venham... Outra coisa é que o %rowcount pega os
> casos de delete que não encontrou dados, MAS casos de erro efetivo no DELETE
> (por exemplo, tentativa de eliminar PK com filhos presentes) ele não pega,
> faltaria adicionar os EXCEPTIONS...
>
> Juntando isso com a necessidade (estranha, mas é um dado do problema) de só
> auditar DELETEs bem-sucedidos, eu optaria por um TRIGGER DE DML, assim :
>
> create or replace trigger TRG_AUD_DEL after DELETE on nomedatabela
> FOR EACH ROW
> BEGIN
> insert into tabeladelog(coluna1, coluna2, ... colunaN)
> values(:old.coluna1, :old.coluna2, ... :old.colunaN);
> END;
>
> ==> aí, além do código ser menor, a "auditoria" vai pegar QUALQUER DELETE na
> tabela, seja ou não vindo do PL/SQL, como a trigger é AFTER (depois, em
> inglês), ela automagicamente só dispara ** DEPOIS ** de um DELETE bem
> sucedido, aí vc não precisa se preocupar no seu código de auditoria com
> COMMITs/ROLLBACKs e ainda é transacional, ie, se depois do delete feito OK
> alguém pedir ROLLBACK do DELETE a inserção na tabela de log não é registrada
> - o colega não diz se isso é preciso mas pelo jeitão parece que sim ...
>
> Aliás, Giovani, coisas do tipo (ah, o que deve ser logado / o que deve
> ocorrer ocorre se neguinho pedir ROLLBACK de um DELETE bem-sucedido, o que
> deve ocorrer se alguém fazer DELETE fora do seu programa, o que deve ocorrer
> se duas sessões pedirem DELETE do mesmo registro , etc, etc) é nossa parte,
> enquanto técnicos especialistas em banco de dados, ter bem claro : o seu
> Cliente com quase 100% de certeza não faz a MENOR IDÉIA dessas coisas, nem
> imagina que o banco é MULTI-USUÁRIO, que podem haver erros de DELETE , etc,
> então quando ele te pede "ah, me faz um log", é um des-serviço vc não o
> avisar dessas coisas , não esclarecer bem a necessidade, o que ele quer que
> aconteça em cada caso ...
>
> []s
>
> Chiappa
>
> --- Em oracle_br@yahoogrupos.com.br <mailto:oracle_br%40yahoogrupos.com.br> ,
> Paulo Petruzalek <ppetruzalek@> escreveu
> >
> > Complementando: acabei deixando na pressa o commit fora do if:
> >
> > If sql%rowcount = 0 then
> > rollback;
> > else
> > commit;
> > end if;
> >
> > Mas uma solução sem commits (dentro da sp) pode ser obtida com savepoints:
> >
> > begin
> > savepoint A;
> > insert into ...
> > delete from ...
> > if sql%rowcount = 0 then
> > rollback to A;
> > end if;
> > end;
> > /
> >
> > Neste caso o controle da transação ficará por conta do código chamador, sem
> > o risco de commitar algo que não devia.
> >
> > Paulo
> >
> > --- Em qua, 28/12/11, Paulo Petruzalek <ppetruzalek@> escreveu:
> >
> > De: Paulo Petruzalek <ppetruzalek@>
> > Assunto: Re: [oracle_br] Tratamento de logs
> > Para: oracle_br@yahoogrupos.com.br <mailto:oracle_br%40yahoogrupos.com.br>
> > Data: Quarta-feira, 28 de Dezembro de 2011, 22:27
> >
> > Se for para tratar o caso de onde o delete não ocorre, basta fazer um:
> >
> > if sql%rowcount = 0 then
> > rollback;
> > end if;
> >
> > Agora no caso de "dar pau", você vai ter que instrumentar o código com um
> > bloco exception. Exemplo simplificado:
> >
> > begin
> > insert into log values (...) ;
> > delete from tabela where x = ...;
> > if sql%rowcount = 0 then rollback;
> > end if;
> > commit;
> >
> > exeception
> > when others then rollback;
> > end;
> > /
> >
> > Talvez seja mais elegante declarar uma exceção e lançar ela no caso do
> > sql%rowcount ser igual a 0... mas enfim, o estilo de código fica por sua
> > conta e/ou pelas regras do seu projeto.
> >
> > Também note que deixar controle de transações (commit / rollback) dentro de
> > uma sp pode causar muitos problemas se esta for chamada por outras sps. Eu
> > não necessariamente usaria esta abordagem, só fiz o exemplo assim para
> > ficar compatível com o seu código.
> >
> > Paulo
> >
> > --- Em qua, 28/12/11, Giovanni Ferreira de Sousa <giovanni.sousa@> escreveu:
> >
> > De: Giovanni Ferreira de Sousa <giovanni.sousa@>
> > Assunto: [oracle_br] Tratamento de logs
> > Para: oracle_br@yahoogrupos.com.br <mailto:oracle_br%40yahoogrupos.com.br>
> > Data: Quarta-feira, 28 de Dezembro de 2011, 10:56
> >
> > Bom Dia galera,
> >
> >
> > Estou com a seguinte situação aqui no trabalho:
> >
> > Um usuário solicitou que para as deleções físicas no banco, esses registros
> > deletado deveriam ser armazendos em uma tabela de log. No schema da
> > aplicação existe uma procedure que faz o delete nas tabelas. Para atender a
> > demanda do usuário foi adicionado na procedure o insert na tabela de log,
> > baseado no registro que será deletado, como segue no exemplo abaixo. Sendo
> > assim gostaria da ajuda de vocês para o seguinte:
> >
> > Após o INSERT é feito o DELETE.
> >
> > SE o DELETE der PAU ou não for feito. Como faço pra dar ROLLBACK(no INSERT
> > anterior)?
> >
> >
> >
> > CREATE OR REPLACE PROCEDURE DBSISMAC.sp_solicitacao_remanej_final (
> > estado IN NUMBER,
> > usuario IN NUMBER,
> > competencia IN NUMBER,
> > DS_USERNAME IN VARCHAR2)
> > IS
> > BEGIN
> > INSERT INTO DBSISMAC.TL_TMQUADRO_06
> > SELECT competencia AS NU_COMPETENCIA_SOLICITACAO,
> > CO_GESTAO,
> > CO_MUNICIPIO_IBGE,
> > CO_CNES,
> > NO_UNIDADE,
> > NU_CONTRATO,
> > DT_PUBLIC_EXT_CONTRATO,
> > VL_TOTAL_FNS,
> > CO_PROTOCOLO,
> > CO_USUARIO,
> > DT_ALTERACAO,
> > DS_USERNAME AS DS_USERNAME,
> > 'EXCLUÍDO' AS DS_OPERACAO,
> > SYSDATE AS DT_OPERACAO
> > FROM DBSISMAC.TM_QUADRO_06
> > WHERE SUBSTR (CO_MUNICIPIO_IBGE, 0, 2) = estado;
> >
> > DELETE FROM DBSISMAC.TM_QUADRO_06
> > WHERE SUBSTR (CO_MUNICIPIO_IBGE, 0, 2) = estado;
> > COMMIT;
> > END;
> > /
> >
> > Atenciosamente,
> >
> > Giovanni
> >
> >
> > [As partes desta mensagem que não continham texto foram removidas]
> >
> >
> >
> > ------------------------------------
> >
> > ----------------------------------------------------------
> > >Atenção! As mensagens do grupo ORACLE_BR são de acesso público e de
> > >inteira responsabilidade de seus remetentes.
> > Acesse: http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/
> > ----------------------------------------------------------
> > >Apostilas » Dicas e Exemplos » Função » Mundo Oracle » Package » Procedure
> > >» Scripts » Tutoriais - O GRUPO ORACLE_BR TEM SEU PROPRIO ESPAÇO! VISITE:
> > >http://www.oraclebr.com.br/
> > ---------------------------------------------------------- Links do Yahoo!
> > Grupos
> >
> >
> >
> >
> > [As partes desta mensagem que não continham texto foram removidas]
> >
> >
> >
> > ------------------------------------
> >
> > ----------------------------------------------------------
> > >Atenção! As mensagens do grupo ORACLE_BR são de acesso público e de
> > >inteira responsabilidade de seus remetentes.
> > Acesse: http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/
> > ----------------------------------------------------------
> > >Apostilas » Dicas e Exemplos » Função » Mundo Oracle » Package » Procedure
> > >» Scripts » Tutoriais - O GRUPO ORACLE_BR TEM SEU PROPRIO ESPAÇO! VISITE:
> > >http://www.oraclebr.com.br/
> > ---------------------------------------------------------- Links do Yahoo!
> > Grupos
> >
> >
> >
> >
> > [As partes desta mensagem que não continham texto foram removidas]
> >
>
>
>
>
>
>
> [As partes desta mensagem que não continham texto foram removidas]
>
