在写入之前，使用另一个表中的值限制字段的值 [英] limit field's value with value from another table before write

问题描述

Postgres TRIGGER 是否默认进行事务处理，例如MySQL中的（我读过）？

Are Postgres TRIGGERs transactional by default like (I've read) in MySQL?

I' ve创建了 TRIGGER 过程，该过程使用简单的 IF 将列的值限制为另一个 TABLE 的值，并随后 UPDATE 如果超出限制。

I've created a TRIGGER procedure that uses a simple IF to limit a column's value with a value from another TABLE with a subsequent UPDATE if the limit is breached.

我希望将它放在一个交易，但是如果我将 IF ... 更新时将换成 BEGIN ... COMMIT ，它会显示错误

I'd prefer that to be in one single TRANSACTION, but if I wrap the IF...THEN UPDATE with BEGIN...COMMIT, it gives error

SQL error:

ERROR:  syntax error at or near ";"
LINE 2:         BEGIN;
                     ^

是 TRIGGER s默认情况下 TRANSACTION al？如果没有，如何将 TRIGGER 做成 TRANSACTION al？

Are TRIGGERs TRANSACTIONal by default? If not, how can a TRIGGER be made TRANSACTIONal?

代码

这是 c>文章：

BEGIN
    BEGIN; /* this first TRANSACTION line causes error */
    IF (NEW.votes_used > (SELECT votes_available FROM vote_totals 
     WHERE vote_totals.user_id = NEW.user_id)) THEN
        UPDATE articles SET votes_used = (SELECT votes_available FROM vote_totals 
         WHERE vote_totals.userID = NEW.user_id) WHERE user_id = NEW.user_id;
    END IF;
    COMMIT; /*last TRANSACTION line */
    RETURN NULL;
END;

我宁愿做一个 CHECK 或 FOREIGN 甚至在数据进入之前就将其扼杀在萌芽状态，但是我不知道如何使用 FOREIGN ，而我读到 CHECK 不能使用子查询。我想我读过这是要走的路，但是我必须透露我是db noob。

I'd MUCH rather do a CHECK or FOREIGN to nip this in the bud before the data even gets in, but I don't know how to do it with a FOREIGN, and I've read that CHECKs can't use subqueries. I think I read that this is the way to go, but I must disclose that I'm db noob.

推荐答案

不需要在触发器内进行 UPDATE 。您可以将值分配给 NEW.votes_used

You do not need UPDATE inside a trigger. You can assign the value to NEW.votes_used

使用类似的内容：

BEGIN
    IF (NEW.votes_used > (SELECT votes_available FROM vote_totals 
     WHERE vote_totals.user_id = NEW.user_id)) THEN
        NEW.votes_used := (SELECT votes_available FROM vote_totals 
         WHERE vote_totals.userID = NEW.user_id);
    END IF;
    RETURN NEW;
END;

或

BEGIN
    NEW.votes_used := LEAST(NEW.votes_used, (SELECT votes_available 
                                             FROM vote_totals 
                                             WHERE vote_totals.userID = NEW.user_id));
    RETURN NEW;
END;

这必须是更新之前触发工作。 （而且所有更新前触发后，它都必须返回新）。

This must be a BEFORE UPDATE trigger to work. (And as all BEFORE UPDATE triggers it must RETURN NEW).

如果要使用触发器模拟检查约束，请尝试以下操作：

If you want to emulate check constraint with trigger - try something like:

BEGIN
    IF (NEW.votes_used > (SELECT votes_available  
                          FROM vote_totals 
                          WHERE vote_totals.user_id = NEW.user_id)) 
    THEN RAISE EXCEPTION 'Not enough votes';
    END IF;
    RETURN NEW;
END;

这篇关于在写入之前，使用另一个表中的值限制字段的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！