Menu de navegação

[torggeParticipante]

Bom dia pessoal.
Estou com uma dúvida em Stored Procedure.
Meu problema: Estamos desenvolvendo um sistema que executa exclusão lógica dos registros da base, porém, eu não posso excluir um registro que é referenciado a uma outra tabela, então, ao tentar excluir um registro eu preciso verificar se ele pode ser excluído ou não. Assim, criei uma SP, que tenta excluir um registro fisicamente. Caso esse registro seja excluido, eu sei que ele não esta sendo usado em outras tabelas, caso contrário ele nao consegue excluir. Gostaria de saber sé é certo executar um ROLLBACK dentro de uma SP e se isso que estou fazendo esta correto ou existe outra forma de fazer.

Segue o código da SP:

CREATE OR REPLACE PROCEDURE VIRGO.ENDERECOS_D (
NID IN OUT NUMBER
)
AS
retorno number;
Begin
retorno := 0;

if nid is not null then
delete from ENDERECOS where ENDERECOS.id = nid;

rollback;
–delete executado com sucesso
retorno := 1;

update enderecos set
enderecos.excluir = retorno
where enderecos.id = nid;

nid := retorno;
else
nid := retorno;
end if;

EXCEPTION
when OTHERS THEN
nid := retorno;

end ENDERECOS_D;

[Leonardo LitzParticipante]

Olá Torgge.

A exclusão lógica busca manter os dados na base de dados, escondendo ele com uma flag de controle.
Bom, acho que seu algoritmo esta legal, só colocaria um save point antes do delete, e mudaria o rollback para até o save point, desta forma vc não irá dar rollback em toda a transação:


if nid is not null then

savepoint s1;

delete from ENDERECOS where ENDERECOS.id = nid;

rollback to s1;

...


E também trataria melhor o erro, indicando o sqlcode específico do erro CHILDEN FOUND.

Uma outra questão que deve ser considerada, e se a chave estrangeira estiver indicando como on delete cascade? Não irá ocorrer o erro de childen found, deverá deletar os filhos normalmente?

Outra forma de se fazer sempre tem, mas acho que esta é a mais simples.

Vlw Leonardo Litz.

[fsitjaParticipante]

Eu acho instead of triggers bastante úteis para esse tipo de solução.
Ela é uma trigger que você coloca sobre uma view e, em vez de realizar a deleção, ela é capaz de fazer um update por exemplo. O usuário nem teria grant na tabela de verdade, ele veria apenas a view e os dados não-excluídos logicamente.

Tudo transparente para a aplicação e o desenvolvedor, que vai escrever um delete normalmente sobre a view, e vai ficar pensando que foi deletado, quando na verdade foi oculto num update.

Vou te dar um exemplo:
Uma tabela pessoa, uma tabela endereço, e uma view sobre cada uma.

A tabela endereço tem uma FK para pessoa. Então no seu caso tem que disparar um erro se existir uma pessoa. Teria que antes excluir a pessoa, mas dá para implementar um cascade delete (na verdade um cascade update setando a deleção lógica) na instead of trigger também, se quiser.

Criação das estruturas

create table tb_pessoa (id_pessoa number primary key,
nm_pessoa varchar2(1000) not null,
dt_exclusao date);

create or replace view vw_pessoa as
select id_pessoa,
nm_pessoa
from tb_pessoa
where dt_exclusao is null
with check option;

create table tb_endereco (id_endereco number primary key,
id_pessoa number references tb_pessoa(id_pessoa) not null,
nm_logradouro varchar2(80) not null,
dt_exclusao date);

create or replace view vw_endereco as
select id_endereco,
id_pessoa,
nm_logradouro
from tb_endereco
where dt_exclusao is null
with check option;

create or replace trigger tg_endereco_io_del
instead of delete on vw_endereco for each row
declare
v_count number;
begin
select count(*)
into v_count
from tb_pessoa p
where p.id_pessoa = :old.id_pessoa
and p.dt_exclusao is null;
if v_count > 0
then
raise_application_error(-20000, 'Pessoa código ' || :old.id_pessoa
|| ' existe em VW_PESSOA. Impossível excluir.');
else
update tb_endereco
set dt_exclusao = sysdate
where id_endereco = :old.id_endereco;
end if;
end;


Execução do exemplo:

SQL> create table tb_pessoa (id_pessoa number primary key,
2 nm_pessoa varchar2(1000) not null,
3 dt_exclusao date);

Tabela criada.

SQL> create or replace view vw_pessoa as
2 select id_pessoa,
3 nm_pessoa
4 from tb_pessoa
5 where dt_exclusao is null
6 with check option;

View criada.

SQL> create table tb_endereco (id_endereco number primary key,
2 id_pessoa number references tb_pessoa(id_pessoa) not null,
3 nm_logradouro varchar2(80) not null,
4 dt_exclusao date);

Tabela criada.

SQL> create or replace view vw_endereco as
2 select id_endereco,
3 id_pessoa,
4 nm_logradouro
5 from tb_endereco
6 where dt_exclusao is null
7 with check option;

View criada.

SQL> create or replace trigger tg_endereco_io_del
2 instead of delete on vw_endereco for each row
3 declare
4 v_count number;
5 begin
6 select count(*)
7 into v_count
8 from tb_pessoa p
9 where p.id_pessoa = :old.id_pessoa
10 and p.dt_exclusao is null;
11 if v_count > 0
12 then
13 raise_application_error(-20000, 'Pessoa código ' || :old.id_pessoa || ' existe e
. Impossível excluir.');
14 else
15 update tb_endereco
16 set dt_exclusao = sysdate
17 where id_endereco = :old.id_endereco;
18 end if;
19 end;
20 /

Gatilho criado.

SQL> create or replace trigger tg_pessoa_io_del
2 instead of delete on vw_pessoa for each row
3 begin
4 update tb_pessoa
5 set dt_exclusao = sysdate
6 where id_pessoa = :old.id_pessoa;
7 end;
8 /

Gatilho criado.

SQL> insert into vw_pessoa values (1, 'MARIA');

1 linha criada.

SQL> insert into vw_endereco values (1, 1,'RUA DA REPUBLICA');

1 linha criada.

SQL> commit;

Commit concluído.

SQL> delete from vw_endereco where id_endereco = 1;
delete from vw_endereco where id_endereco = 1
*
ERRO na linha 1:
ORA-20000: Pessoa código 1 existe em VW_PESSOA. Impossível excluir.
ORA-06512: at "FSITJA.TG_ENDERECO_IO_DEL", line 11
ORA-04088: error during execution of trigger 'FSITJA.TG_ENDERECO_IO_DEL'

SQL> delete from vw_pessoa where id_pessoa = 1;

1 linha deletada.

SQL> delete from vw_endereco where id_endereco = 1;

1 linha deletada.

SQL> select * from vw_pessoa;

não há linhas selecionadas

SQL> select * from vw_endereco;

não há linhas selecionadas

SQL> select * from tb_pessoa;

ID_PESSOA NM_PESSOA DT_EXCLU

---

     1 MARIA     10/12/09

SQL> select * from tb_endereco;

ID_ENDERECO ID_PESSOA NM_LOGRADOURO DT_EXCLU

---

      1          1 RUA DA REPUBLICA                 10/12/09

SQL>

Edit: repare que para quem está executando o delete, não há como saber se foi deletado ou não o registro na view. O Oracle “mente” para o usuário que deletou 1 registro, e ele não consegue mais vê-lo a seguir.

Obviamente, identifiquei com um prefixo VW as views e TB nas tabelas, por clareza, mas se você preferir ocultar essa informação e a política de nomenclatura de sua empresa permitir, faça como achar melhor.

No caso de usar via stored procedure, você vai ter que combinar com seu desenvolvedor que ele deve respeitar um protocolo, onde precisa executar a stored procedure antes de tentar deletar, depois fazer um update no campo x em vez de excluir os registros. Há um fardo adicional no desenvolvedor, que tem que se preocupar o tempo todo em como seria o “jeito certo” de fazer a exclusão lógica. Com a instead of trigger ele não precisa se preocupar com isso, e o procedimento fica a prova de burradas, desde que protegidas as tabelas por baixo das views e que a implementação das triggers seja mantida corretamente atualizada.

Algo mais rebuscado poderia ser feito usando VPD’s, mas aí depende muito de sua versão do Oracle, além de precisar da enterprise edition, se não estou enganado. Em suma, é muito mais poderoso, mas também mais complexo.

[torggeParticipante]

Obrigado pela ajuda pessoal !

[fsitjaParticipante]

Disponha. Qualquer dúvida adicional, pode perguntar.

[]s
Francisco

[Autor]

Posts