触发错误时的回滚事务 [英] Rollback Transaction on Trigger ERROR

问题描述

我正在尝试检查要插入系统的房间在那个日期是否已经出租.我已经计算过与房间号和日期都匹配的行，然后回滚了事务.但是，即使我更改了代码以引发用户定义的异常，也遇到了以下错误:

I'm trying to check if the room that is going to be inserted in the system is already rented at that date or not. I've though about counting the rows that match both the room number and the date, and then rolling back the transaction. But I'm getting the following error, even though I have changed the code to raise user-defined exceptions:

ERROR:  cannot begin/end transactions in PL/pgSQL
HINT: Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT: PL/pgSQL function "checkRoom"() line 17 at SQL statement

CREATE OR REPLACE FUNCTION "checkRoom"() RETURNS TRIGGER AS
$BODY$
DECLARE 
    counter integer;
  BEGIN
    SELECT COUNT("num_sesion")
    FROM "Sesion"
    INTO counter
    WHERE "Room_Name"=NEW."Room_Name" AND "Date"=NEW."Date";

    IF (counter> 0) THEN -- Probably counter>1 as it's triggered after the transaction..
        raise notice 'THERE'S A ROOM ALREADY!!';
        raise exception 'The room is rented at that date';
    END IF;
    RETURN new;
EXCEPTION
    WHEN raise_exception THEN
        ROLLBACK TRANSACTION;
        RETURN new;
END;$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;

然后创建触发器:

CREATE TRIGGER "roomOcupied" AFTER INSERT OR UPDATE OF "Room_Name", "Date"
ON "Sesion" FOR EACH ROW
EXECUTE PROCEDURE "checkRoom"();

距离我上次使用SQL的时间已经两年了，plsql和plpgsql之间的变化让我发疯.

It's been 2 years from my last approach to SQL and the changes between plsql and plpgsql are getting me crazy.

推荐答案

触发函数有几个问题:

• 使用 IF EXISTS (...) THEN 而不是计算所有出现的次数.更快，更简单.参见:

• Use IF EXISTS (...) THEN instead of counting all occurrences. Faster, simpler. See:

• PL/pgSQL检查行是否存在

触发函数 AFTER INSERT OR UPDATE可以仅返回NULL. RETURN NEW仅与称为 BEFORE 的触发器相关. 手册:

A trigger function AFTER INSERT OR UPDATE can just return NULL. RETURN NEW is only relevant for triggers called BEFORE. The manual:

对于在操作后触发的行级触发器，返回值将被忽略，因此它们可以返回NULL.

不平衡的单引号.

Unbalanced single quote.

如 @Pavel解释所示，您无法从plpgsql函数中控制事务.任何未处理的异常都将强制您的整个事务自动回滚.因此，只需删除EXCEPTION块.

As @Pavel explained, you cannot control transactions from within a plpgsql function. Any unhandled exception forces your entire transaction to be rolled back automatically. So, just remove the EXCEPTION block.

您的假设触发器被重写:

Your hypothetical trigger rewritten:

CREATE OR REPLACE FUNCTION check_room()
  RETURNS TRIGGER AS
$func$
BEGIN
   IF EXISTS (
         SELECT FROM "Sesion"    -- are you sure it's not "Session"?
         WHERE  "Room_Name" = NEW."Room_Name"
         AND    "Date" = NEW."Date") THEN
     RAISE EXCEPTION 'The room is rented at that date';
   END IF;
   RETURN NULL;
END
$func$  LANGUAGE plpgsql;

BEFORE 触发器更有意义.

A BEFORE trigger makes more sense.

但是，UNIQUE INDEX ON ("Room_Name", "Date")可以更有效地执行相同的操作.然后，任何违反的行都会引发重复的键异常并回滚事务(除非被捕获并处理).在现代的Postgres中，您可以选择使用INSERT ... ON CONFLICT ...跳过或转移此类INSERT尝试.参见:

But a UNIQUE INDEX ON ("Room_Name", "Date") would do the same, more efficiently. Then, any row in violation raises a duplicate key exception and rolls back the transaction (unless caught and handled). In modern Postgres you can alternatively skip or divert such INSERT attempts with INSERT ... ON CONFLICT .... See:

• 如何进行UPSERT(合并) ，在PostgreSQL中插入...在重复更新中?)?

高级用法:

• 如何在ON CONFLICT中使用RETURNING在PostgreSQL中?

这篇关于触发错误时的回滚事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！