一个简短的现实世界触发器示例

来自 PostgreSQL Wiki
跳转到导航跳转到搜索

一个简短的现实世界触发器示例

创建于 2003-03-13，作者 Richard Huxton ([email protected])

版本: 初稿 - 谨慎使用

这是一个现实世界示例，展示了如何使用 plpgsql 过程语言构建一个触发器函数，以强制执行超出外键所能提供的完整性。表格已被简化为本示例所需的最小程度，但代表了真实项目中的真实表格。

数据库

  • 我们有一组产品，每个产品都属于某种类型。
  • 我们有一组服务器，每个服务器只能提供特定类型的产品。
  • 服务器提供产品的子集，相同的产品可以从多个服务器获得。

这为我们提供了类似的表格结构

product (pr_id, pr_type)
server (svr_id, pr_type)
server_products (svr_id, pr_id)

我们可以使用外键来确保 server_products 具有有效的 svr_id 和 pr_id，但如果我们想要强制执行产品的类型，则需要检查两个表格，因为重要的是产品中的 pr_type 与服务器中的对应类型匹配。

方案 1 (在理想世界中...)

一个简单的解决方案是定义一个视图 possible_server_products，它将包含 svr_id、pr_type 和 pr_id，然后引用它。不幸的是，PostgreSQL 不能将外键检查与视图进行比较，只能与真实表格进行比较。这不是关系理论的理论限制，但有一些复杂的实现问题，所以它不太可能很快发生。

我会描述如何实现针对视图的外键，但这里没有足够的空间 ;-).

方案 2 (嗯，这不正常...)

如果我们稍微改变一下定义，使我们拥有 server_products (svr_id,pr_type,pr_id)，我们可以使用一个引用表格 server 中 (svr_id,pr_type) 的外键，以及另一个引用产品中 (pr_type,pr_id) 的外键，这正是我们想要的。

不幸的是，我们现在需要在插入新产品时在我们的应用程序中查找 pr_type。我们可以通过定义一个看起来像我们原始版本的 server_products 的视图并编写规则来为我们执行查找来避免这种情况。

但是，这个解决方案有一个更根本的问题 - 我们在 server_products 的每一行中都有一个冗余的 pr_type。它是此表格的主键的一部分，还是取决于主键？好吧，我们的主键显然是 (svr_id,pr_id)，因为这标识了行。但是 - pr_type 不依赖于此键，它仅依赖于 svr_id (或者仅依赖于 pr_id，这取决于你想要如何看待它)。这违反了第二范式 (2NF)，我喜欢规范化的数据库，所以这种方案不可接受。

方案 3 (这是我之前做的...)

所以 - 我们不想更改表格定义，但想要强制执行产品类型。为此，我们需要手动添加三个触发器（每个参与的表格一个）以及一个或多个函数来强制执行我们的约束。

在这种情况下，我选择使用一个函数来处理所有三个触发器。你可以为三个不同的函数提出一个很好的论据，但将所有代码放在一个地方可以降低我更改数据库结构时忘记更改某些内容的可能性。

假设我们已经运行了 createlang 实用程序，我们将使用以下方法定义我们的函数

CREATE OR REPLACE FUNCTION check_srv_prodtype() RETURNS trigger AS
...code here...
LANGUAGE 'plpgsql';

在函数中，我们需要检查 TG_RELNAME 伪变量的值，它告诉我们哪个表格触发了对我们的调用。然后，我们可以检查 NEW 伪记录的内容，以查看这些值是否可接受。如果可以接受，我们返回 NEW，否则我们返回 NULL。检查对 server_products 的更改的代码片段将类似于

IF TG_RELNAME=''server_products'' THEN
SELECT pr_type INTO prod_type FROM possible_server_products WHERE svr_id=NEW.svr_id AND pr_id=NEW.pr_id;
IF FOUND THEN
RETURN NEW;
ELSE
RETURN NULL;
END IF;
ELSE...

-- Definition of possible_server_products is:
CREATE VIEW possible_server_products AS
SELECT s.svr_id, s.pr_type, p.pr_id FROM server s, product p WHERE p.pr_type=s.pr_type;

在实际函数中，除了返回 NULL 并添加一些注释之外，我们还想生成错误消息。然后，我们可以设置触发器来调用我们的函数。

CREATE TRIGGER check_server_products
AFTER INSERT OR UPDATE ON server_products
FOR EACH ROW EXECUTE PROCEDURE check_srv_prodtype();

这告诉 PG 在对表格 server_products 进行插入或更新时，在其他检查之后但事务提交之前调用我们的函数。如果我们说 BEFORE INSERT... 函数将在运行任何外键检查之前被调用。

此解决方案的完整列表位于本文档末尾。

方案 3 的问题

没有完美的系统。以下解决方案有两个主要问题。

首先，函数需要对所有三个表格中插入或修改的每一行运行，并且它对每次测试都运行查询。如果你有很多经常修改的行，这将是一个性能问题。编写用 C 语言编写的函数可能是一个选择，但由于函数非常简单，因此收益可能很小。

其次，函数本身可能存在错误。由于我们无法使用内置功能满足完整性要求，因此我们别无选择，只能编写一些代码，但它应该经过测试。例如，在这个函数的初稿中，我忘记了测试对 server 表的更改，并且只有在测试时才发现了这一点。

参考文献

外键引用 在 CREATE TABLE 页面上。

The createlang 实用程序。

CREATE TRIGGER 引用。

plpgsql 中的触发器函数。

对 范式 的概述，或作为 pdf。

SQL 脚本

下面的 SQL 用于重新创建此示例 - 你应该将其剪切并粘贴到文本编辑器中并将其保存为 briefex_trigger.txt。然后，你可以在 psql 中使用 \i briefex_trigger.txt 运行它。它已在 PG v7.3 上进行了测试，因此如果你遇到问题，请确保在剪切和粘贴过程中没有出现任何奇怪现象。

注意 - 以下列表中的一些引号似乎存在问题。函数中的所有引号都应该加倍，任何单引号都是错误的。

DROP TABLE product CASCADE;
DROP TABLE server CASCADE;
DROP VIEW possible_server_products CASCADE;
DROP VIEW actual_server_products CASCADE;
DROP TABLE server_products CASCADE;
CREATE TABLE product (
pr_id int NOT NULL,
pr_type int NOT NULL,
PRIMARY KEY (pr_id)
);
CREATE TABLE server (
svr_id varchar(4) NOT NULL,
pr_type int NOT NULL,
PRIMARY KEY (svr_id)
);
CREATE TABLE server_products (
svr_id varchar(4) NOT NULL REFERENCES server (svr_id),
pr_id int NOT NULL REFERENCES product (pr_id),
PRIMARY KEY (svr_id, pr_id)
);
-- Now add some triggers to check pr_type is valid for servers
-- 
CREATE VIEW possible_server_products AS
SELECT s.svr_id, s.pr_type, p.pr_id FROM server s, product p WHERE p.pr_type=s.pr_type;
CREATE VIEW actual_server_products AS
SELECT s.svr_id, s.pr_type, sp.pr_id FROM server s, server_products sp WHERE s.svr_id=sp.svr_id;
CREATE OR REPLACE FUNCTION check_srv_prodtype() RETURNS trigger AS '
DECLARE
prod_id int4;
prod_type int4;
server_id varchar(4);
BEGIN
IF TG_RELNAME=''server_products'' THEN
-- check product can be allocated to this server
SELECT pr_type INTO prod_type FROM possible_server_products WHERE svr_id=NEW.svr_id AND pr_id=NEW.pr_id;
IF FOUND THEN
-- product type is valid for this server
RETURN NEW;
ELSE
SELECT pr_type INTO prod_type FROM product WHERE pr_id=NEW.pr_id;
IF FOUND THEN
RAISE EXCEPTION ''Server % does not support products of type % (product id = %)'', NEW.svr_id, prod_type, NEW.pr_id;
ELSE
-- Need this in case we are called from BEFORE trigger
-- in which case foreign key check has not happened
RAISE EXCEPTION ''Server % does not support non-existent products (product id = %)''
, NEW.svr_id, NEW.pr_id;
END IF;
RETURN NULL;
END IF;
ELSIF TG_RELNAME=''product'' THEN
-- Inserting/updating a "product"
SELECT svr_id INTO server_id FROM actual_server_products WHERE pr_id=NEW.pr_id AND pr_type<>NEW.pr_type;
IF NOT(FOUND) THEN
-- this product is either not used or the new type is valid where it is used.
RETURN NEW;
ELSE
SELECT pr_type INTO prod_type FROM server WHERE svr_id=server_id;
RAISE EXCEPTION ''Server % uses product % and only allows product type %''
, server_id, NEW.pr_id, prod_type;
RETURN NULL;
END IF;
ELSE
-- Must be updating a "server", see if there are any products for it.
SELECT pr_id INTO prod_id FROM server_products WHERE svr_id=NEW.svr_id;
IF found THEN
-- Have products, so no change to pr_type allowed.
IF OLD.pr_type<>NEW.pr_type THEN
SELECT pr_type INTO prod_type FROM product WHERE pr_id=prod_id;
RAISE EXCEPTION ''Server % uses product % and so requires product type %'
, NEW.svr_id, prod_id, prod_type;
RETURN NULL;
END IF;
END IF;
-- All OK, either no server_products or type isnt changed
RETURN NEW;
END IF;
END;'
LANGUAGE 'plpgsql';
CREATE TRIGGER check_server_products
AFTER INSERT OR UPDATE ON server_products
FOR EACH ROW EXECUTE PROCEDURE check_srv_prodtype();
CREATE TRIGGER check_used_product_type
AFTER INSERT OR UPDATE ON product
FOR EACH ROW EXECUTE PROCEDURE check_srv_prodtype();
CREATE TRIGGER check_server_product_type
AFTER INSERT OR UPDATE ON server
FOR EACH ROW EXECUTE PROCEDURE check_srv_prodtype(); -- OK, now try inserting some data
INSERT INTO product VALUES (1,1);
INSERT INTO product VALUES (2,2);
INSERT INTO product VALUES (3,1);
INSERT INTO product VALUES (4,2);
INSERT INTO server VALUES ('a',1);
INSERT INTO server VALUES ('b',2);
INSERT INTO server_products VALUES ('a',1);
INSERT INTO server_products VALUES ('a',3);
-- Next insert should fail
INSERT INTO server_products VALUES ('a',2);
-- And this should fail
UPDATE product SET pr_type=2 WHERE pr_id=1;
-- As should this
UPDATE server SET pr_type=2 WHERE svr_id='a';
检索自 "https://wiki.postgresql.ac.cn/index.php?title=A_Brief_Real-world_Trigger_Example&oldid=21480"