postgresql - 如何在触发函数或条件时获取更新语句

Question

CREATE TABLE accounts (
 id SERIAL PRIMARY KEY,
 email TEXT,
 verified BOOLEAN DEFAULT FALSE,
 ...
);

CREATE OR REPLACE FUNCTION fn_reset_verified() RETURNS TRIGGER AS $$
BEGIN 
IF (
    OLD.email != NEW.email -- do nothing if no change
) THEN
    RAISE NOTICE 'UPDATE';
    UPDATE users SET verified=FALSE WHERE id=NEW.id;
END IF;
RETURN NEW;
END; $$ language 'plpgsql';

CREATE TRIGGER reset_verified AFTER UPDATE OF email ON accounts
FOR EACH ROW
WHEN (OLD.email != NEW.email) -- only when email changed
EXECUTE PROCEDURE fn_reset_verified();

基于上述架构，我希望创建触发器以在用户更改电子邮件时自动更新验证为 false。它适用于以下语句:

UPDATE accounts SET email='newemail@email.com' WHERE id=1;

当我打算更新电子邮件并将验证设置为 TRUE 时，我的问题就出现了，如下所示:

UPDATE accounts SET email='verified@email.com', verified=TRUE WHERE id=1;

如果在update语句中设置了verified，我应该跳过这个函数。可能条件如下:

WHEN (OLD.email != NEW.email AND STATEMENT.verified IS NULL)

那么有什么办法可以得到更新语句或者涉及的列呢？

Answer 1

首先，您永远不应该在该表的更新触发器中对该表调用 UPDATE。很可能你会无限循环(不是在这种特殊情况下，但无论如何)并且通常有更好的方法来实现同样的事情。

在您的情况下，您将制作一个 BEFORE UPDATE 触发器，当电子邮件地址更改时触发，并检查/修改该触发器中的 verified 列:

CREATE OR REPLACE FUNCTION fn_reset_verified() RETURNS TRIGGER AS $$
BEGIN 
    -- Below condition does not have to be checked because of WHEN clause in trigger
<strike>IF (
    OLD.email != NEW.email -- do nothing if no change
) THEN</strike>
    RAISE NOTICE 'UPDATE';
    <b>NEW.verified := ...;</b>
<strike>END IF;</strike>
    RETURN NEW;
END; $$ language 'plpgsql';

CREATE TRIGGER reset_verified <b>BEFORE</b> UPDATE OF email ON accounts
FOR EACH ROW
WHEN (OLD.email != NEW.email) -- only when email changed
EXECUTE PROCEDURE fn_reset_verified();

但是，没有逻辑来确定将 NEW.verified 设置为触发器内部的内容。问题是，NEW 变量保存新行的所有列的值在用新值设置列后，而不仅仅是那些被命令修改的值。因此，如果 NEW.verified = true，则无法知道调用触发器的命令是否是:

UPDATE accounts SET email = 'newemail@email.com', verified = true WHERE id = 1;

或

UPDATE accounts SET email = 'newemail@email.com' WHERE id = 1;

因此 NEW.verified 可以引用旧电子邮件地址或新电子邮件地址，这些地址在 UPDATE 命令中被特别标记为已验证。

你唯一的选择是改变你的逻辑:如果电子邮件地址改变了，之后总是需要验证，所以 NEW.verified = false。