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]
