-- 昨天看到臉書 Backend TW 社群中,有人提出資料庫刪除資料的方式.
-- 大部分是選 增加一個欄位,做軟刪除.至於這個方式的好壞,另外再討論.
-- 使用 trigger 的方式來記錄,卻是相對比較少人選擇,或是根本不知道有此方式.
-- 在此先做一個 MySQL trigger 簡單的範例,給大家參考.

create table girls (
  galid int unsigned not null auto_increment primary key
, name varchar(20) not null
);

insert into girls (galid, name) values
(1, '初音みのり'),
(2, '桜木凛'),
(3, '希崎ジェシカ'),
(4, '葵つかさ');

create table girls_update_archive (
  id int unsigned not null auto_increment primary key
, update_at timestamp not null default current_timestamp
, galid int unsigned not null
, name varchar(20) not null
);

create table girls_delete_archive (
  id int unsigned not null auto_increment primary key
, delete_at timestamp not null default current_timestamp
, galid int unsigned not null
, name varchar(20) not null
);

-- 建立 after update trigger
delimiter $$
create trigger girls_au
after update on girls
for each row
begin
  insert into girls_update_archive 
  (galid, name) values
  (old.galid, old.name);
end
$$
delimiter ;

-- 測試 trigger
update girls
   set name = '桜木凛 人妻'
 where galid = 2;

select *
  from girls;

+-------+--------------------+
| galid | name               |
+-------+--------------------+
|     1 | 初音みのり         |
|     2 | 桜木凛 人妻        |
|     3 | 希崎ジェシカ       |
|     4 | 葵つかさ           |
+-------+--------------------+
4 rows in set (0.00 sec)

select *
  from girls_update_archive;
+----+---------------------+-------+-----------+
| id | update_at           | galid | name      |
+----+---------------------+-------+-----------+
|  1 | 2020-05-25 10:30:39 |     2 | 桜木凛    |
+----+---------------------+-------+-----------+
1 row in set (0.00 sec)

-- 接著來做 after delete trigger; 程式碼基本上一樣.
delimiter $$
create trigger girls_ad
after delete on girls
for each row
begin
  insert into girls_delete_archive 
  (galid, name) values
  (old.galid, old.name);
end
$$
delimiter ;

-- 測試 delete trigger
delete
  from girls
 where galid >= 3;
 
select *
  from girls;
+-------+------------------+
| galid | name             |
+-------+------------------+
|     1 | 初音みのり       |
|     2 | 桜木凛 人妻      |
+-------+------------------+
2 rows in set (0.00 sec)

select *
  from girls_delete_archive;
+----+---------------------+-------+--------------------+
| id | delete_at           | galid | name               |
+----+---------------------+-------+--------------------+
|  1 | 2020-05-25 10:41:16 |     3 | 希崎ジェシカ       |
|  2 | 2020-05-25 10:41:16 |     4 | 葵つかさ           |
+----+---------------------+-------+--------------------+
2 rows in set (0.00 sec)

-- 可以觀察到 trigger 很好的幫我們保留了資料,並且紀錄了易動了時刻.
-- 也有只使用單一table來紀錄的方式, 有興趣的讀者,可以先自行試試看,明天再將此方式刊出.