DanBrother的部落格

 防止某table被誤刪除的方法(Prevent Row Deletion Accidentally) :

-- Package
create or replace package count_rows_pck is
  v_rows number default 0; 
  procedure reset_count;    --  Reset Counter
  procedure add_count;   -- Counter Plus One
  function get_count return number;   --  Get Counter
end count_rows_pck;
/

-- Package body
create or replace package body count_rows_pck
is
  procedure reset_count
  is
    begin v_rows:=0;  -- Reset Counter
  end;
 
  procedure add_count
  is
  begin
    v_rows:=nvl(v_rows,0)+1;  -- Counter Plus One
  end;
 
  function get_count return number
  is
  begin
     return v_rows;  --  Get Counter
  end;
end count_rows_pck;
/

-- Before Trigger (Statement Level)
create or replace trigger t_before_delete_test_table
  before delete on test_table
begin
   count_rows_pck.reset_count;
end;
/

-- After Trigger (Row Level)
create or replace trigger t_after_delete_test_table_A
  after delete on test_table
  for each row
declare
begin
   count_rows_pck.add_count;
end;
/

-- After Trigger (Statement Level)
create or replace trigger t_after_delete_test_table_B
  after delete on test_table
declare
v_rows number default 0;
begin
   v_rows:=count_rows_pck.get_count;
   if v_rows >= 100 then
      raise_application_error(-20001,'Deletion of too many rows are not allowed!');
   end if;
end;
/

** 測試結果 **
1. 
-- 建立資料表
create table test_table
(num number(5)
);

2.
-- 寫入10000筆資料
begin
   for i in 1..10000 loop
          insert into test_table(num)
          values(i);
   end loop;
commit;
end;
/

select count(*) from test_table;
  COUNT(*)
----------
     10000

3.
-- 測試刪除99筆資料,結果成功!
SQL> delete from test_table where rownum
已刪除 99 個資料列.

SQL> rollback;

4.
-- 測試誤刪所有資料,不被允許
SQL> delete from test_table;
delete from test_table
            *
 ERROR 在行 1:
ORA-20001: Deletion of too many rows are not allowed!
ORA-06512: 在 "T_AFTER_DELETE_TEST_TABLE_B", line 6
ORA-04088: 執行觸發程式 'T_AFTER_DELETE_TEST_TABLE_B' 時發生錯誤

SQL> select count(*) from test_table;
  COUNT(*)
----------
     10000