当我第一次听说 SQL 触发器时,我 12 岁。我的兄弟乔纳森刚刚在一家初创公司开始了他的软件生涯。有一天,乔纳森回家时对一个充满复杂 SQL 触发器的数据库感到沮丧。

由于我唯一的编程经验是在 VB6 中重新创建我最喜欢的视频游戏,我几乎没有什么安慰。

快进 16 年,现在我可以从我哥哥的角度来看。在开源启动风格的全栈开发领域(想想 Django、Rails、Javascript、PHP、MySQL、Postgres..),ORM 非常流行,而 SQL 触发器等功能则远没有那么传统。

但是 SQL 触发器仍然有价值。在我开发类似 ERP 的定制软件期间,SQL 触发器是一个非常宝贵的工具。在构建高度面向数据的软件时,尤其是当数据具有财务性质并且对准确性要求很高时,您更有可能看到数据在较低级别以更直接的方式被操纵。

本文包含我希望与我的兄弟分享的关于如何有效使用 SQL 触发器的所有信息。

目录

• 什么是 SQL 触发器?

• 如何创建 SQL 触发器 - 语法

• Postgres 触发器示例 1:创建时钟

• Postgres 触发器示例 2:创建审计表

• 触发器的其他注意事项

什么是 SQL 触发器?

SQL 触发器,也称为数据库触发器,允许您告诉您的 SQL 引擎(对于这些示例,Postgres)在事件发生时,甚至在事件发生之前运行一段代码。

在 Postgres 中,您通过创建返回类型为trigger的函数来描述要运行的代码。在其他一些引擎(如 MySQL)中,代码块是触发器的一部分和内部。

在我讨论不同的事件类型是什么以及创建触发器的特定语法之前,为什么要使用数据库触发器?

使用 SQL 触发器的优点

保持数据完整性

数据库触发器有多种用途,并且是整理严格数据完整性的绝佳工具。如果您有其他应用程序服务器或访问数据库的用户不知道应用程序中编码的特定业务逻辑,则替代解决方案(如 Django 的模型挂钩)可能会失败。

分离业务逻辑

在应用程序代码中编码关键业务逻辑也会在业务逻辑更新时出现问题。如果您有业务需求将传入的数字乘以 10,并且您想修改此逻辑以将数字乘以 20,则更改 SQL 中的逻辑将保证从该确切部署时间开始的每条数据都会受到以下因素的影响新的逻辑。

SQL 服务器充当单一事实点。如果在多个应用程序服务器上实现逻辑,您就不能再期望行为发生干净、明确的变化。

原子事务

自然原子性是与触发器捆绑在一起的另一个理想特性。由于事件和触发器函数都是一个原子事务的一部分,因此您绝对可以肯定地知道,如果事件触发,触发器将触发。他们是一个完美的 SQL 婚姻。

如何创建 SQL 触发器 - Postgres 语法

以下是为您的数据库创建触发器的组件:

1.触发事件类型

2. 活动前后

3.触发器的效果

[](https://res.cloudinary.com/practicaldev/image/fetch/s--eaiZtk4C--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/ https://blog.arctype.com/content/images/2021/01/Screen-Shot-2021-01-10-at-23.30.24.png)

SQL 触发器剖析

触发事件类型

数据库触发器将监视表中的特定事件。以下是可以激活触发器的不同事件的一些示例:

• 更改数据:INSERT、UPDATE、DELETE

数据库触发器还可以列出多个这些事件。

如果UPDATE是列出的事件之一,您可以传入应该激活触发器的列列表。如果您不包括此列表,则更新任何列都会激活它。

触发器BEFORE或AFTER

触发器可以运行BEFORE或AFTER个事件。

如果您想阻止像INSERT这样的事件,您将需要运行BEFORE。如果您需要确定事件确实会发生,AFTER是理想的。

触发效果

触发器可以按行运行,也可以按语句运行。假设您运行一个UPDATE语句,该语句更改了表中的 5 行。

如果在触发器中指定FOR EACH ROW,那么触发器将运行 5 次。如果您指定FOR EACH STATEMENT,那么它只会运行一次。

当然,我们不能忘记触发器激活时要运行的实际代码。在 Postgres 中,被放置在一个函数中并与触发器分开。将触发器与其运行的代码分开可以创建更简洁的代码,并允许多个触发器执行相同的代码。

Postgres 触发器示例 #1:创建时钟

时钟记录员工何时下班并计算他/她的总工作时间。让我们创建一个示例时钟,看看我们如何使用触发器来防止员工输入无效数据。

设置数据库架构

此模式的设计将每次进出视为单独的事件。每个事件都是time_punch表中的一行。或者,您也可以让每个员工“轮班”一个事件,并将打卡时间和打卡时间存储在一行中。

在以后的文章中,我将深入探讨如何定义强大的数据库模式。注册 Arctype并将 SQL/数据库指南和提示直接发送到您的收件箱!

对于这个例子,我已经为我们的表定义了模式。下面的代码创建了一个employee和time_punch表,并为新员工 Bear 插入了一些打卡数据。

create table employee (
  id serial primary key,
  username varchar
);

create table time_punch (
  id serial primary key,
  employee_id int not null references employee(id),
  is_out_punch boolean not null default false,
  punch_time timestamp not null default now()
);

insert into employee (username) values ('Bear');
insert into time_punch (employee_id, is_out_punch, punch_time)
values
(1, false, '2020-01-01 10:00:00'),
(1, true, '2020-01-01 11:30:00');

进入全屏模式 退出全屏模式

Bear 上午 10:00 打卡上班,上午 11:30 下班(漫长的一天工作)。让我们编写一个 SQL 查询来计算 Bear 工作了多长时间。

在这里稍作停顿,考虑一下在给定我们的模式并仅使用 SQL 的情况下您将如何解决这个问题。

---

使用SQL计算工作时间

我决定的解决方案查看每个“出”拳并将其与之前的“入”拳相匹配。

select tp1.punch_time - tp2.punch_time as time_worked
from time_punch tp1
join time_punch tp2
on tp2.id = (
    select tps.id
    from time_punch tps
    where tps.id < tp1.id
    and tps.employee_id = tp1.employee_id
    and not tps.is_out_punch
    order by tps.id desc limit 1
)
where tp1.employee_id = 1
and tp1.is_out_punch

进入全屏模式 退出全屏模式

time_worked
-------------
 01:30:00
(1 row)

进入全屏模式 退出全屏模式

在这个查询中,我选择了所有出拳,然后将它们加入到最接近的前面的“入”拳中。减去时间戳,我们得到 Bear 每班工作了多少小时!

此模式的问题之一是您可以连续插入多个“入”或“出”打孔。对于我们创建的查询,这会引入歧义,从而导致计算不准确,并且员工获得的报酬多于或少于应有的报酬。

SQLINSERT BEFORE触发器示例 - 保持数据完整性

我们需要一些东西来防止输入/输出模式被打断。不幸的是,检查约束只查看正在插入或更新的行,而不能考虑其他行的数据。

这是使用数据库触发器的完美情况!

让我们创建一个触发器来防止INSERT事件破坏我们的模式。首先,我们将创建“触发函数”。这个函数是当检测到事件类型时触发器将执行的。

触发器函数的创建与常规 Postgres 函数类似,不同之处在于它返回trigger。

create or replace function fn_check_time_punch() returns trigger as $psql$
  begin
    if new.is_out_punch = (
      select tps.is_out_punch
      from time_punch tps
      where tps.employee_id = new.employee_id
      order by tps.id desc limit 1
    ) then
      return null;
    end if;
    return new;
  end;
$psql$ language plpgsql;

进入全屏模式 退出全屏模式

new关键字表示要插入的行的值。它也是您可以返回以允许插入继续的对象。或者,当返回null时,这将停止插入。

此查询查找之前的time_punch并确保其输入/输出值与插入的值不同。如果值相同,则触发器返回null,并且不记录 time_punch。否则,触发器返回new并允许insert语句继续。

现在我们将该函数作为触发器链接到time_punch表。BEFORE在这里至关重要。如果我们将此触发器作为AFTER触发器运行,它将运行太晚而无法停止插入。

create trigger check_time_punch before insert on time_punch for each row execute procedure fn_check_time_punch();

进入全屏模式 退出全屏模式

让我们尝试插入另一个“出”拳:

insert into time_punch (employee_id, is_out_punch, punch_time)
values
(1, true, '2020-01-01 13:00:00');

进入全屏模式 退出全屏模式

Output:
INSERT 0 0

进入全屏模式 退出全屏模式

我们可以从输出中看到,触发器阻止了为同一员工插入两个后续出拳。

也可以从触发器引发异常,以便您的应用程序(或运行 SQL 查询的人)收到失败通知,而不是插入计数简单地为 0。

Postgres 触发器示例 #2:创建审计表

准确存储员工打卡数据对企业至关重要。这种类型的数据通常最终会直接转化为员工的工资,而另一方面则是公司的工资成本。

由于这些数据的重要性,假设公司希望能够在发现异常时重新创建表的所有历史状态。

审计表通过跟踪对表的每次更改来实现这一点。当主表上的一行被更新时,一行将被插入到审计表中,记录其过去的状态。

我将使用我们的time_punch表来演示如何使用触发器创建一个自动更新的审计表。

创建审计表

有几种方法可以保存审计或历史记录表。让我们创建一个单独的表来存储过去的时间状态_punch。

create table time_punch_audit (
  id serial primary key,
  change_time timestamp not null default now(),
  change_employee_id int not null references employee(id),
  time_punch_id int not null references time_punch(id),
  punch_time timestamp not null
);

进入全屏模式 退出全屏模式

该表存储:

• 冲头更新时间

• 更新它的员工

• 被改变的打孔ID

• 打孔更新前打孔时间

在我们创建触发器之前,我们首先需要在我们的time_punch表中添加一个change_employee_id列。这样,触发器将知道哪个员工对time_punch表进行了每次更改。

alter table time_punch add column change_employee_id int null references employee(id);

进入全屏模式 退出全屏模式

(不向 time_punch 添加任何列的替代解决方案可能是撤销此表的更新权限,并强制此数据库的用户使用自定义函数,如update_time_punch(id, change_user_id, ...))

SQLUPDATE AFTER触发器示例-插入数据

在我们的time_punch table发生更新后,此触发器运行并将OLD打卡时间值存储在我们的审计表中。

create or replace function fn_change_time_punch_audit() returns trigger as $psql$
  begin
    insert into time_punch_audit (change_time, change_employee_id, time_punch_id, punch_time)
    values
        (now(), new.change_employee_id, new.id, old.punch_time);
    return new;
  end;
$psql$ language plpgsql;


create trigger change_time_punch_audit after update on time_punch
for each row execute procedure fn_change_time_punch_audit();

进入全屏模式 退出全屏模式

NOW()函数从 SQL 服务器的角度返回当前日期和时间。如果这与实际应用程序相关联,则您需要考虑传递用户实际发出请求的确切时间,以避免延迟引起的差异。

在更新触发期间,NEW对象表示如果更新成功,该行将包含哪些值。您可以使用触发器“拦截”插入或更新,只需将您自己的值分配给NEW对象即可。OLD对象包含更新前的行值。

让我们看看它是否有效!我已经插入了第二个名为 Daniel 的用户,他将成为 Bear 的时间拳的编辑。

select punch_time
from time_punch
where id=2;

进入全屏模式 退出全屏模式

     punch_time      
---------------------
 2020-01-01 11:30:00
(1 row)

进入全屏模式 退出全屏模式

我将运行下面的查询两次以模拟 2 次将时间增加 5 分钟的编辑。

update time_punch set punch_time = punch_time + interval '5 minute', change_employee_id = 2 where id = 2;

进入全屏模式 退出全屏模式

这是审计表,反映了过去的打卡时间:

        change_time | username | punch_time      
----------------------------+----------+---------------------
 2021-01-06 20:10:56.44116 | Daniel | 2020-01-01 11:35:00
 2021-01-06 20:10:55.133855 | Daniel | 2020-01-01 11:30:00

进入全屏模式 退出全屏模式

触发器的其他注意事项

使用数据库触发器需要注意以下几点:

1.随着时间的推移维护触发器

2.连接触发逻辑

3. 开发人员专业知识

随着时间的推移维护触发器

应用程序代码中的业务逻辑随着时间的推移通过git或其他源代码控制系统而自然地记录下来。开发人员很容易看到代码库中的一些逻辑并快速执行git log并查看更改列表。

使用 SQL 触发器和函数来管理随时间的变化更加复杂,标准化程度较低,并且需要更多的思考和计划。

连接触发逻辑

触发器还可以触发其他触发器,从而使看似无辜的INSERT或UPDATE的结果迅速复杂化。这种风险也是具有副作用的应用程序代码所固有的。

开发人员专业知识

一些开发人员圈子对触发器的认识也低得多,因此引入它们会增加新开发人员成功开展项目所需的培训投资。

SQL 最初可能是一种笨拙且令人沮丧的语言,因为您学习构建查询的许多模式都是从您如何以过程语言提取数据的方式“由内而外”地学习的。

使用触发器升级您的 SQL 游戏

我希望这些示例能够帮助您更好地理解数据库触发器。我在使用触发器解决可追溯性、一致性和准确性至关重要的数据问题方面取得了巨大成功。

决定在您的应用程序中引入触发器是经过深思熟虑后做出的决定 - 我希望您有机会实现和探索 SQL 最有趣和最有趣的功能之一!

如果您有兴趣继续提高您的 SQL 技能,请考虑查看Arctype。 Arctype 是一个现代 SQL 编辑器,旨在简化使用数据库和 SQL 的过程。加入我们不断发展的社区并立即下载 Arctype。