为了安全敏感的设计,我想禁止在某些表上执行DELETE
操作。
DELETE
操作只应在行上设置一个deleted
标记(该标记将在视图上可见,并由应用程序层使用)。
据我所知,规则会生成额外的查询语句,因此规则不能抑制原始查询。
以下是一个玩具示例,使用触发器(尚未测试):
-- data in this table should be 'undeletable'
CREATE table article (
id serial,
content text not null,
deleted boolean default false
)
-- some view that would only show articles, that are NOT deleted
...
-- toy trigger (not tested)
CREATE OR REPLACE FUNCTION suppress_article_delete()
RETURNS TRIGGER AS $sad$
BEGIN
IF (TG_OP = 'DELETE') THEN
UPDATE article SELECT id, content, TRUE;
-- NEW or NULL??
RETURN NEW;
END IF;
RETURN NULL;
END;
$sad$ LANGUAGE plpgsql;
如何有效地禁用 DELETE
操作?
INSTEAD
规则。 - miku