在PostgreSQL 9.4中，如何在事务完成后触发而不是在每行上触发触发器？

Question

我有一个触发器，以确保所有投票的总数不超过100：

CREATE FUNCTION limit_votes() RETURNS TRIGGER AS $limit_votes$
    DECLARE
        votes_total int;
    BEGIN
        votes_total := NEW.vote + (SELECT SUM(vote) FROM cars WHERE portfolio_id = NEW.portfolio_id AND car != NEW.car);
        IF votes_total > 100 THEN
            RAISE EXCEPTION 'votes_limit_exceeded';
        END IF;
        RETURN NEW;
    END;
$limit_votes$ LANGUAGE plpgsql;
CREATE TRIGGER limit_votes BEFORE INSERT OR UPDATE ON cars FOR EACH ROW EXECUTE PROCEDURE limit_votes();

需要提到的是，“投票”可以被更新，因此不仅仅是INSERT。

这就是我的问题所在，因为一个用户可能会在汽车之间更改票数，然后触发器将被作为更新发出，导致单行更新的限制超过100，在其他更新降低其他票数之前。

希望这有意义。

我希望触发器在BEGIN ... COMMIT之后被调用，这样我可以在检查所有内容是否正确之前更新多行 - 但如果用户尝试将投票数更新为超过100，则还需引发异常并不保存结果。

- Michael Nielsen

Answer 1

触发器必须是一个约束和“后”触发器。然后您可以使用 DEFERRABLE：

CREATE CONSTRAINT TRIGGER limit_votes
AFTER INSERT OR UPDATE ON cars
DEFERRABLE
FOR EACH ROW EXECUTE PROCEDURE limit_votes();

请查看文档：

http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html

- freakish

---

更改为"CREATE TRIGGER limit_votes BEFORE INSERT OR UPDATE ON cars DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE limit_votes();"失败，出现语法错误在或靠近"DEFERRABLE"。 - Michael Nielsen

当触发器是AFTER触发器时，如果更新了某些“非法”的内容会发生什么？它仍然会被丢弃吗？ - Michael Nielsen

@MichaelNielsen 是的，我认为它会回滚整个事务。但是请确认以确保100%正确。 - freakish

只有在添加 "INITIALLY DEFERRED" 时，这段代码才能正常工作：CREATE CONSTRAINT TRIGGER limit_votes AFTER INSERT OR UPDATE ON cars DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE limit_votes(); - bettwäsh

它需要 INITIALLY DEFERRED。来自文档： INITIALLY IMMEDIATE INITIALLY DEFERRED 如果约束可延迟，则此子句指定检查约束的默认时间。如果约束为 INITIALLY IMMEDIATE，则在每个语句之后进行检查。这是默认设置。如果约束为 INITIALLY DEFERRED，则仅在事务结束时进行检查。可以使用 SET CONSTRAINTS 命令更改约束检查时间。 - Jordan Silva

Answer 2

根据https://www.postgresql.org/docs/13/sql-createtrigger.html，由于这应该是一个可延迟的约束，并且您想在事务之后触发过程执行，所以您需要使用以下内容：

INITIALLY DEFERRED

。

CREATE CONSTRAINT TRIGGER limit_votes
AFTER INSERT OR UPDATE ON cars
DEFERRABLE
INITIALLY DEFERRED 
FOR EACH ROW EXECUTE PROCEDURE limit_votes();

INITIALLY IMMEDIATE

如果我们想要在每个语句执行后触发过程执行。

CREATE CONSTRAINT TRIGGER limit_votes
AFTER INSERT OR UPDATE ON cars
DEFERRABLE
INITIALLY IMMEDIATE 
FOR EACH ROW EXECUTE PROCEDURE limit_votes();

- M A Hossain Tonu

Answer 3

虽然现有的回答已经解决了 "在事务之后运行触发器" 的部分，但该函数仍将针对每一行运行。为确保它只运行一次，您可以按照以下方式在额外的 triggers 表上添加一个中间触发器：

-- triggers table for logging and firing a trigger only once
drop table if exists triggers;
create table triggers(
    "tx_id" bigint primary key,
    "name" text,
    "exec_count" int default 0
);

-- the original trigger now fires an insert into the triggers table
create constraint trigger cars_insert_update
after insert or update on cars
deferrable
initially deferred
for each row 
execute function insert_trigger();

-- trigger function to insert a row in the triggers table
create or replace function insert_trigger()
returns trigger as $insert_trigger$
begin
    insert into triggers
    values (txid_current(), tg_name, 0)
    on conflict (tx_id)
    do nothing;
    
    return null;
end
$insert_trigger$ language plpgsql;

-- trigger to run the original trigger function
-- it will only fire once since updates are ignored
drop trigger if exists triggers_insert on triggers;
create constraint trigger triggers_insert
after insert on triggers
deferrable
initially deferred
for each row
when (new."name" = 'cars_insert_update')
execute function limit_votes();

- Johan Maes

---

如果您想按语句运行触发器，可以使用 for each statement 而不是 for each row。 - user330315

不过，如果它是一个约束（延迟）触发器，则不会执行。 - Johan Maes