fsitjaEu 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.