写入前用另一个表中的值限制字段的值 [英] 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?

我创建了一个 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.

我更喜欢将其放在一个 TRANSACTION 中，但是如果我将 IF...THEN UPDATE 与 包装在一起>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 的 TRANSACTION 是默认的吗?如果没有，如何使 TRIGGER 成为 TRANSACTIONal?

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

代码

这是文章的ON INSERT OR UPDATE:

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;

这必须是 BEFORE UPDATE 触发器才能工作.(并且作为所有 BEFORE UPDATE 触发器，它必须 RETURN NEW).

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屋！