以Postgresql为主,再聊聊资料库 MySQL Trigger 实例应用 1

-- 昨天看到脸书 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 triggerdelimiter $$create trigger girls_auafter update on girlsfor each rowbegin  insert into girls_update_archive   (galid, name) values  (old.galid, old.name);end$$delimiter ;-- 测试 triggerupdate 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_adafter delete on girlsfor each rowbegin  insert into girls_delete_archive   (galid, name) values  (old.galid, old.name);end$$delimiter ;-- 测试 delete triggerdelete  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来纪录的方式, 有兴趣的读者,可以先自行试试看,明天再将此方式刊出.