Öncelikle bu konuya nereden geldiğimi bikaç cümle ile aktarayım. Geçen hafta blogdaki iletişim bilgilerim üzerinden bir arkadaşım dan bana ulaştı ve kullandıkları bir database’ deki tüm kullanıcıların ortak bir user kullandığından ve kimi dataların güvenliğinden endişe ettiğinden bahsetti. Tabiki bu problemin en güzel çözümü user bazlı connection kurulmasının sağlanması sonrasında user bazlı yetkilendirme ve hatta resource manager ile resource groupları oluşturularak yönetilmesidir. Ancak uygulamada bunun çok mümkün olmadığı yapılarda var. Mailde arkadaşın istemiş olduğu yardım kimi tablolar için belirlemiş olduğu kayıt adedinden fazla kaydın silinmesinin engellenmesi şeklinde idi.
Aşağıdaki örnekde de bu soruna üretmiş olduğum bir çözümden bahsetmek istiyorum. Belki bir yerlerde başka arkadaşlarada yardımı dokunabilir düşüncesiyle buradan da paylaşmak istedim.
Örneğimiz de kullanmak üzere bir tablo create edelim ;
SQL> create table kamil.t as select * from dba_tables
Table created.
İşlemi yapabilmek için öncelikle delete edilmek istenilen tablodaki kayıt sayısını bulup, delete edilecek olan data sayısı ile karşılaştırmamız gerekiyor bunun için öncelikle aşağıdaki paketi oluşturuyoruz;
create or replace package kamil.pkg_kayit_sayisi is
NUMROWS number;
procedure proc_baslangic_kayit_sayisi;
procedure proc_kayit_ekleme;
function proc_kayit_sayisini_bulma
return number;
function GET_TABLECOUNT
return number;
end pkg_kayit_sayisi;
/
create or replace package body kamil.pkg_kayit_sayisi as
procedure proc_baslangic_kayit_sayisi is
begin
NUMROWS := 0;
end;
procedure proc_kayit_ekleme is
begin
NUMROWS := Nvl(NUMROWS, 0) + 1;
end;
function proc_kayit_sayisini_bulma
return number is
begin
return NUMROWS;
end;
function GET_TABLECOUNT
return number is
v_count NUMBER := 0;
begin
select count(*) into v_count from kamil.t ;
return v_count;
end;
end pkg_kayit_sayisi;
/
Sonra tablo üzerindeki delete işlemini kontrol edebilmek amacıyla aşağıdaki triggerları create ediyoruz ;
———1
create or replace trigger kamil.before_delete_kontrol
before delete
on kamil.t
begin
kamil.pkg_kayit_sayisi.proc_baslangic_kayit_sayisi;
end;
———– 2
create or replace trigger kamil.after_delete_kontrol
after delete
on kamil.t
for each row
begin
kamil.pkg_kayit_sayisi.proc_kayit_ekleme;
end;
/
———- 3
create or replace trigger kamil.tablo_delete_kontrol
after delete
on kamil.t
declare
v_count NUMBER := 0;
v_deletecnt NUMBER := 0;
v_oran NUMBER(15,2) := 0;
begin
v_deletecnt := kamil.pkg_kayit_sayisi.proc_kayit_sayisini_bulma;
v_count := kamil.pkg_kayit_sayisi.func_kayit_sayisini_getirme ;
v_oran := ROUND(v_deletecnt /( v_count+v_deletecnt) , 2);
if v_count+v_deletecnt = 0 then
return;
end if;
–Dbms_output.PUT_LINE(sys.pkg_kayit_sayisi.proc_kayit_sayisini_bulma || ‘ rows were affected.’||’—count:’||sys.pkg_kayit_sayisi.func_kayit_sayisini_getirme);
if v_oran > 0.1 then
raise_application_error( -20001, ‘Bu tablo üzerindeki delete islemi kontrol edilmektedir. Tablodaki data sayisinin % ‘||v_oran ||’ silmeye calismaktasiniz. %10 nundan fazlasini silemezsiniz !!’);
rollback;
end if;
end;
Artık test için hazırız. Yukarıdaki örnekde silinecek kayıt adedi tablonun toplam adedinin %10’ undan büyük olması durumunda hata vermesini bekliyoruz. Burdaki rakam istenildiği şekilde artırılıp azaltılabilir.
Öncelikle tablodaki kayıt sayısına bakalım ;
SQL>select count (*) from kamil.t
COUNT(*)
———-
2787
1 row selected.
Tablomuzda 2787 adet data var. Dolayısıyla ben bu tablodan %11’ veya daha fazlasını silmeye çalıştığımızda hata almamız gerekiyor. Bakalım ;
SQL>delete from kamil.t where rownum <= 293
delete from kamil.t where rownum <= 293
Error at line 1
ORA-20001: Bu tablo üzerindeki delete islemi kontrol edilmektedir. Tablodaki data sayisinin % .11 silmeye calismaktasiniz. %10 nundan fazlasini silemezsiniz !!
ORA-06512: at “KAMIL.TABLO_DELETE_KONTROL”, line 16
ORA-04088: error during execution of trigger ‘KAMIL.TABLO_DELETE_KONTROL’
Daha az bir kaydı silmeye çalışalım ;
SQL> delete from kamil.t where rownum <= 250 250 rows deleted. Bir problem olmadan çalıştı. Gelelim bu işlemin uygulanabilirliğine, öyle zannediyorumki bu tarz bir yöntemi hiçbir dba kolay kolay uygulamaz. Hatta böyle bir talebe sıcak bile bakmaz. Çünkü bu tarz triggerlar performans anlamında ciddi sıkıntılar doğurabilmektedir. Silinecek tablo çok büyük bir tablo ise her bir delete komutu çalıştığında önce tablo size’ ını hesaplayacak sonra delete sayısına bakıp sonrasında çalıştırılacak olması, kabul edilebilir bir durum olmayacaktır. Hele hele OLTP sistemlerde kesinlikle böyle çözümlere hiç girmemek gerekir. Ben sadece istenirse bu tarz bir çözümünde olabileceğini göstermek amacıyla paylaşmak istedim.
Yukardaki örnekde delete komutu için gösterilmiştir. Delete olan kısımlar update veya insert olarak değiştirildiğinde hatta INSERT OR DELETE OR UPDATE şeklinde değiştirildiğinde tüm DML işlemler için bu koşul çalıştırılmış olacaktır.
