在 PostgreSQL 上的 INSERT 或 UPDATE 之前检查给定值的触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22746741/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trigger for checking a given value before INSERT or UPDATE on PostgreSQL
提问by CMJunior
I have to create a trigger to update a database where there are products. Products have an expiration date and before inserting or updating a product I must check if the expirationDate is superior to the current timestamp. If it is I must do the insert/update regularly (this is what I have problems with). If not I just simply ignore it.
我必须创建一个触发器来更新有产品的数据库。产品有一个到期日期，在插入或更新产品之前，我必须检查 expireDate 是否优于当前时间戳。如果是，我必须定期进行插入/更新（这是我遇到的问题）。如果不是，我只是简单地忽略它。
Here's the code that I have written.
这是我编写的代码。
CREATE FUNCTION product_expiration_date()
RETURNS trigger AS $BODY$
BEGIN
IF new.expirationDate > CURRENT_TIMESTAMP THEN
INSERT INTO Product VALUES (new).*;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER verify_expiration_date BEFORE INSERT OR UPDATE ON Product
FOR EACH ROW EXECUTE PROCEDURE product_expiration_date();
回答by Daniel Vérité
Your trigger function should return NEWwhen the condition to check is satisfied and you want the INSERT/UPDATE to happen, otherwise NULLto skip the operation, or better, raise an exception with a specific error message.
NEW当要检查的条件满足并且您希望 INSERT/UPDATE 发生时，您的触发器函数应该返回，否则NULL跳过该操作，或者更好的是，引发带有特定错误消息的异常。
It must not execute itself the INSERT it was called for, this will be done by the SQL engine with the values in NEW.
它不能自己执行它被调用的 INSERT，这将由 SQL 引擎使用NEW.
Code to skip the operation:
跳过操作的代码：
IF new.expirationDate > CURRENT_TIMESTAMP THEN
RETURN NEW;
ELSE
RETURN NULL;
END IF;
Or with an exception:
或者有一个例外：
IF new.expirationDate > CURRENT_TIMESTAMP THEN
RETURN NEW;
ELSE
RAISE EXCEPTION 'Invalid expiration date';
END IF;
See Overview of Trigger Behaviorin the doc for more.
有关更多信息，请参阅文档中的触发器行为概述。
回答by Vlad Mihalcea
CHECK constraint
检查约束
As I explained in this article, the easiest way to achieve this goal is via the CHECKconstraint, which is an SQL standard feature.
正如我在本文中解释的那样，实现此目标的最简单方法是通过CHECK约束，这是 SQL 标准功能。
ALTER TABLE Product
ADD CONSTRAINT verify_expiration_date_check
CHECK (expirationDate < CURRENT_TIMESTAMP)
Trigger function
触发功能
You could also use a BEFORE INSERT or UPDATEtrigger, but that's more suitable when the validation rule needs to inspect multiple tables, not just the current on that triggered the check.
您也可以使用BEFORE INSERT or UPDATE触发器，但当验证规则需要检查多个表时更适合，而不仅仅是触发检查的当前表。
Now, if you really want to use a trigger function, this is how it would look like in your case
现在，如果您真的想使用触发器功能，这就是您的情况
First, you need to create the trigger function:
首先，您需要创建触发器函数：
CREATE OR REPLACE FUNCTION product_expiration_date()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.expirationDate > CURRENT_TIMESTAMP
THEN
RAISE EXCEPTION 'Product [id:%] expired!',
NEW.id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Notice that we are throwing an exception using RAISEif the expirationDateis greater than the current timestamp.
请注意，RAISE如果expirationDate大于当前时间戳，我们将抛出异常。
Now, you need to pass this trigger function to an INSERT and UPDATE trigger:
现在，您需要将此触发器函数传递给 INSERT 和 UPDATE 触发器：
CREATE TRIGGER verify_expiration_date
BEFORE INSERT OR UPDATE ON Product
FOR EACH ROW EXECUTE PROCEDURE product_expiration_date();
Although you could use a trigger function, it's much easier to use a CHECKconstraint. For more details about PostgreSQL trigger functions, check out this article.
Although you could use a trigger function, it's much easier to use a CHECKconstraint. For more details about PostgreSQL trigger functions, check out this article.
