# PostgreSQL trigger 應用 保護table 並記錄 然後發送 telegram 通知

之前我們介紹了利用PostgreSQL trigger 的機制來保護table 不被update delete的方法,請參考:
[點我前往](https://hackmd.io/@pgsql-tw/BJNU4QDYd)

現在我們來做些加強,除了保護以外,並增加記錄的功能.
先介紹將出現的 table 及 fucntion.

1. it0523b , it0523c 被保護的table
2. reject_log 紀錄被保護table 的delte , update 操作時間
3. f_reject_change() 用來保護的函數,會被trigger 呼叫
4. f_reject_log_modify() 將紀錄寫入 reject_log, 會被trigger 呼叫

### 建立 table, function, trigger

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

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

create table reject_log (
  id uuid not null default gen_random_uuid()
, ts timestamp not null default clock_timestamp()
, msg text
);

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

create or replace function f_reject_log_modify()
returns trigger
language plpgsql as
$code$
begin
  insert into reject_log(msg) values
  (tg_table_schema || '.' || tg_table_name);
  return null;
end;
$code$;

-- triggers
create trigger tr_prot_it0523b
before update or delete
on it0523b
for each row
execute function f_reject_change();

create trigger tr_log_it0523b
after update or delete
on it0523b
for each statement
execute function f_reject_log_modify();

create trigger tr_prot_it0523c
before update or delete
on it0523c
for each row
execute function f_reject_change();

create trigger tr_log_it0523c
after update or delete
on it0523c
for each statement
execute function f_reject_log_modify();

commit;

跟之前使用 for each row 的 trigger,這次使用 for each statement 的方式.

### 輸入資料及測試

insert into it0523b(val)
select generate_series(1, 3);

delete 
  from it0523b
 where val = 3;

delete 
  from it0523b;

update it0523b
   set val = val + 1;

select * from reject_log;

                  id                  |             ts             |     msg
--------------------------------------+----------------------------+-------------
 cde86518-1c50-46d3-a051-0cfe803e791d | 2021-05-23 18:13:05.846341 | s12.it0523b
 88851fd0-212f-4d64-888e-c87852c651e9 | 2021-05-23 18:13:37.667343 | s12.it0523b
 1a224679-e80c-4e66-aad5-27d2ce955e36 | 2021-05-23 18:14:14.480931 | s12.it0523b
(3 rows)

### 測試結果討論
可以觀察到三次操作均有被紀錄下來, 以 schema.table 的方式紀錄了.

## 主動通知的方式
除了紀錄以外,若能夠主動通知就更理想了.
目前常用的通訊軟體除了Line, 還有 telegram .
以下將以 telegram 為例,至於 telegram bot 的 token以及chatid的設定取得方式,網路上有很多,在此不贅述.

我們將使用一個 plpython 函數,然後建立一個 rejct_log 的after insert trigger , 來達到此通知功能.

create or replace function f_send_reject_log()
returns trigger
language plpython3u as
$code$
  import requests
  
  bot_token = 'use the force'
  bot_chatID = 'luke'
  res = plpy.execute("select current_database()")
  curtdb = res[0]["current_database"]
  mesg = '有人想變動 database:' + curtdb + ' table: '  + TD["new"]["msg"]
  send_text = 'https://api.telegram.org/bot' + bot_token + '/sendMessage?chat_id=' + bot_chatID + '&parse_mode=Markdown&text=' + mesg
  requests.get(send_text)
  
$code$;

create trigger tr_send_reject_log
after insert
on reject_log
for each row
execute function f_send_reject_log();

#### 測試

update it0523b
   set val = val + 1;

insert into it0523c(val)
select generate_series(1, 3);

update it0523c
   set val = val + 1;

#### 收到通知的畫面

## 結論
由上面的結果來觀察,我們後續要增加被保護的table,無需再新建立function,只要再被保護的table 建立兩個trigger,呼叫原來的fucntion,就會保護以及紀錄,而紀錄的table又會驅動trigger function來做通知.
PotgreSQL這樣的機制是很有彈性及靈活,這幾天有另外介紹了 extension temporal_tables 及 extension periods 都可以見到此種方式的應用,只需要通用funtion,就能搭配許多table.
Trigger 在國內的應用較少討論,希望這幾篇event trigger 以及 trigger 的應用,能夠帶給大家一些參考.