PostgreSQL 8.2:要求特定的列出现在UPDATE语句中 [英] PostgreSQL 8.2: Require specific column to be present in UPDATE statement
问题描述
我想强迫用户指定某个表(sometbl)的更新来源,例如
。指定本地或远程(对于col2)-但是在执行 UPDATE
语句时应在数据库级别检查该要求,这样:
I would like to force user to specify origin of update to some table (sometbl),
eg. to specify 'local' or 'remote' (for col2) - but checking of that requirement should occur at DB level when UPDATE
statement is executed so:
UPDATE sometbl SET col1 = 'abc';
应该抛出错误(异常),但是:
should throw error (exception), but:
UPDATE sometbl SET col1 = 'abc', col2 = 'remote';
...将会成功。
我尝试为该表创建BEFORE更新触发器,但无法检查
NEW.col2是否已明确设置。
I tried to create BEFORE update trigger for that table, but I was unable to check if NEW.col2 was explictly set.
我用过的条件
IF NEW.col2 IS NULL THEN
RAISE EXCEPTION 'you must specify source of this update (local/remote)'
END IF;
但是每次在更新中未指定col2时(UPDATE sometbl SET col1 ='abc')
but every time, when col2 was not specified in update (UPDATE sometbl SET col1 = 'abc')
我在NEW.col2伪变量中获得了该字段的当前值,而不是假定的NULL。
I got current value of that field in NEW.col2 pseudo-var, instead of supposed NULL.
推荐答案
有几个解决方法,可以防止在UPDATE stmt中不存在指定字段时UPDATING行吗? href = http://www.postgresql.org/docs/8.2/static/trigger-definition.html rel = nofollow noreferrer>触发?一个会在更新之前运行,并将该列设置为null。如果更新仍然在之后运行,则呕吐(返回NULL)(如果该列仍为空)。 (如果触发器返回NULL,则更新失败。)
How about a couple of triggers? One runs before the update, and sets the column to null. One runs after the update and pukes (returns NULL) if the column is still null. (If a trigger returns NULL, the update fails.)
这篇关于PostgreSQL 8.2:要求特定的列出现在UPDATE语句中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!