PostgreSQL trigger 應用 保護table資料不變動

就是只能insert, 不能delete 或 update.

測試用的table

create table it0523 (
  id uuid not null default gen_random_uuid()
, ts timestamp not null default clock_timestamp()
, val int not null
);

trigger function

create or replace function f_reject_modify()
returns trigger
language plpgsql as
$code$
begin
  return null;
end;
$code$;

trigger 定義

create trigger tr_prot_it0523
before update or delete
on it0523
for each row
execute function f_reject_modify();

注意到 trigger function 是可以沿用的.
要保護其他table, 只需要建立trigger , 取不同名稱, 名稱取自己喜歡的, on tablename , tablename 是想保護的就可以了.

測試

insert into it0523(val)
select generate_series(1, 5);

select * from it0523;
                  id                  |             ts             | val
--------------------------------------+----------------------------+-----
 01c02938-ef40-4f09-9865-98ee682bff3d | 2021-05-23 08:31:52.081319 |   1
 c05278e9-0875-4fa3-a0c5-eb18dbcd7a71 | 2021-05-23 08:31:52.090581 |   2
 5978eea2-0304-4e49-a289-7d3bfcfa524d | 2021-05-23 08:31:52.090609 |   3
 b3fd1c28-1334-4475-bfc8-b2f718f84db2 | 2021-05-23 08:31:52.090615 |   4
 857d9cca-ec03-4354-88b6-f32b818d4d96 | 2021-05-23 08:31:52.090629 |   5
(5 rows)

--
delete 
  from it0523
 where val = 3;

DELETE 0

select count(*) from it0523;

update it0523
   set val = val + 1;

UPDATE 0

--

insert into it0523(val) values (6);

INSERT 0 1

select * from it0523;

                  id                  |             ts             | val
--------------------------------------+----------------------------+-----
 01c02938-ef40-4f09-9865-98ee682bff3d | 2021-05-23 08:31:52.081319 |   1
 c05278e9-0875-4fa3-a0c5-eb18dbcd7a71 | 2021-05-23 08:31:52.090581 |   2
 5978eea2-0304-4e49-a289-7d3bfcfa524d | 2021-05-23 08:31:52.090609 |   3
 b3fd1c28-1334-4475-bfc8-b2f718f84db2 | 2021-05-23 08:31:52.090615 |   4
 857d9cca-ec03-4354-88b6-f32b818d4d96 | 2021-05-23 08:31:52.090629 |   5
 3b22bdee-03e9-465d-9999-cd0526e77dc2 | 2021-05-23 08:36:20.975553 |   6
(6 rows)

測試後討論

可以觀察到 delete, update 都被擋住, insert 是可以的.但是沒有回饋訊息.
這種是 安靜模式

有回饋訊息的方式

很簡單,我們只需要將 trigger function 增加回饋訊息.

create or replace function f_reject_modify()
returns trigger
language plpgsql as
$code$
begin
  raise info '不能變更!';
  return null;
end;
$code$;

就加了 raise info '不能變更!';
trigger 都不需要重新定義.

測試

delete 
  from it0523
 where val = 3;
 
INFO:  00000: 不能變更!
DELETE 0

update it0523
   set val = val + 1;

INFO:  00000: 不能變更!
INFO:  00000: 不能變更!
INFO:  00000: 不能變更!
INFO:  00000: 不能變更!
INFO:  00000: 不能變更!
INFO:  00000: 不能變更!
UPDATE 0

可以觀察到,回饋訊息會 each row 都列印訊息.

結論

今天介紹了保護資料的trigger,實作方式很簡單.
有回饋資訊的模式,可以在開發測試階段應用,
當系統穩定時,可以變為安靜模式.
或是建立兩種trigger function , 視情況靈活搭配使用.
加上PG 的trigger 與 trigger function 是分開的方式.
使用上會很靈活.

同場加映 MySQL 的方式

就不多做說明,直接看code

create table it0523 (
  id int not null auto_increment primary key
, ts timestamp not null default current_timestamp
, val int not null
);



delimiter $$
create trigger trbu_prot_it0523
before update
on it0523
for each row
begin
  signal sqlstate '45000' set message_text = '本Table 禁止 update';
end
$$
delimiter ;


delimiter $$
create trigger trbd_prot_it0523
before delete
on it0523
for each row
begin
  signal sqlstate '45000' set message_text = '本Table 禁止 delete';
end;
$$
delimiter ;

---
insert into it0523 (val) values
(1),(2),(3);

delete
  from it0523
 where id = 2;

ERROR 1644 (45000): 本Table 禁止 delete

update it0523
   set val = val + 1;
   
ERROR 1644 (45000): 本Table 禁止 update

select *
  from it0523;

+----+---------------------+-----+
| id | ts                  | val |
+----+---------------------+-----+
|  1 | 2021-05-23 14:42:48 |   1 |
|  2 | 2021-05-23 14:42:48 |   2 |
|  3 | 2021-05-23 14:42:48 |   3 |
+----+---------------------+-----+
3 rows in set (0.00 sec)