使用statement trigger 檢查delete 是否有where條件

前言

之前有分享過使用trigger 保護的table不能delete或update.按我前往
也有搭配JSON保存資料的方式.按我前往
也有一些其他方式的分享,例如保護並發送telgram訊息的方式.在此不一一列舉.有興趣的朋友,可以看 PostgreSQLTW_HackMD 在HackMD的分享資料.
不久前MySQL User Group Taiwan有一篇有意思的分享.按我前往
會出現一些提示語.
之前分享的方式,大多是純保護,這次增加判斷是否有where 條件.
並使用statement 層級,避免出現過多訊息.

來看展示吧

create schema s0125;

create table s0125.tbl1 (
  id int not null primary key
, gal text
);

insert into s0125.tbl1 values
(1, '宇都宮しをん'),(2, 'RION'), (3, '安齋らら');

create or replace function s0125.thinkabout()
returns trigger
language plpgsql 
as $code$
begin
  if ( current_query() !~* 'where' ) then
    raise exception '兄弟別衝動! 想想 %', (array['父母', '孩子', '小島南', '河北彩花'])[(mod(ceil(random()*10)::int, 4) + 1)];
    return null;
  else
    return new;
  end if;
end;
$code$;

create trigger tri_bd_tbl1
before delete
on s0125.tbl1
for each statement
execute function s0125.thinkabout();

-- 注意是 statement trigger
delete 
  from s0125.tbl1;

ERROR:  P0001: 兄弟別衝動! 想想 河北彩花
CONTEXT:  PL/pgSQL function s0125.thinkabout() line 4 at RAISE
LOCATION:  exec_stmt_raise, pl_exec.c:3907
Time: 1.197 ms
bunko666[bunko666]#* select * from s0125.tbl1;
 id |     gal
----+--------------
  1 | 宇都宮しをん
  2 | RION
  3 | 安齋らら
(3 rows)

Time: 0.321 ms
bunko666[bunko666]#* delete
bunko666-*#   from s0125.tbl1;
ERROR:  P0001: 兄弟別衝動! 想想 父母
CONTEXT:  PL/pgSQL function s0125.thinkabout() line 4 at RAISE
LOCATION:  exec_stmt_raise, pl_exec.c:3907
Time: 0.492 ms
bunko666[bunko666]#* delete
bunko666-*#   from s0125.tbl1;
ERROR:  P0001: 兄弟別衝動! 想想 小島南
CONTEXT:  PL/pgSQL function s0125.thinkabout() line 4 at RAISE
LOCATION:  exec_stmt_raise, pl_exec.c:3907
Time: 0.430 ms
bunko666[bunko666]#* rollback;
ROLLBACK

就是要刪除

delete 
  from s0125.tbl1
 where true;

DELETE 3

select * from s0125.tbl1;
 id | gal
----+-----
(0 rows)

只要有where 條件,就可以動作了.
這樣就可以避免,沒有使用where時不小心刪除全部資料.

結語

結合之前一些例子,我們可以搭配使用,做很多保護或者保存後還原.

感謝

感謝 河北彩花,小島南以及宇都宮三姐妹.