在数据库维护过程中，有时候有这么一种需求，对指定表只允许查询和插入操作，而不允许修改和删除，一般来说，这种需求比较少见，但是在数据迁移，或者数据表维护时会有这样的需求；最近有个项目做数据迁移时就碰到了这种需求，此文主要介绍下在 PostgreSQL 中实现上述需求的方法，其实现方式有两种。

方法一: 创建规则

在 PostgreSQL 中支持在 table 或者 view 上创建规则，实现命令的转换，简单的说当对表上执行操作时，可以转换成其它指定的命令; 解释起来破为费力，下面的例子将有助于理解。

1.1 创建测试表

francs=> create table test_rule (id int4 primary key,name varchar(32));  
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_rule_pkey" for table "test_rule"  
CREATE TABLE

francs=> insert into test_rule select generate_series(1,10),'a';  
INSERT 0 10

francs=> select * from test_rule;  
id | name  
----+------  
 1 | a  
 2 | a  
 3 | a  
 4 | a  
 5 | a  
 6 | a  
 7 | a  
 8 | a  
 9 | a  
10 | a  
(10 rows)

1.2 创建 update 规则

1	create or replace rule rul_test_rule_update as on update to test_rule do instead nothing;

备注：上面命令意思为在表 test_rule 上创建了一个规则，当在这张表上执行 update 操作时，啥也不干。

1.3 update 测试

francs=> select * From test_rule where id=1;  
id | name  
----+------  
 1 | a  
(1 row)

francs=> update test_rule set name='aaa' where id=1;  
UPDATE 0

francs=> select * From test_rule where id=1;  
id | name  
----+------  
 1 | a  
(1 row)

备注：在表 test_rule 上创建了 update 规则后，那么之后在这张表上执行 update 操作时，实际上什么命令都没执行，但也没抛出 ERROR。

1.4 同理创建 delete 规则

francs=> create or replace rule rul_test_rule_delete as on delete to test_rule do instead nothing;  
CREATE RULE

francs=> /d test_rule  
 Table "francs.test_rule"  
Column | Type | Modifiers  
--------+-----------------------+-----------  
id | integer | not null  
name | character varying(32) |  
Indexes:  
 "test_rule_pkey" PRIMARY KEY, btree (id)  
Rules:  
 rul_test_rule_delete AS  
 ON DELETE TO test_rule DO INSTEAD NOTHING  
 rul_test_rule_update AS  
 ON UPDATE TO test_rule DO INSTEAD NOTHING

备注：同理，在表 test_rule 上创建 delete 规则，当在表 test_rule 上执行 delete 操作时，啥也不干。

1.5 delete 测试

francs=> select * From test_rule where id=1;  
id | name  
----+------  
 1 | a  
(1 row)

francs=> delete from test_rule where id=1;  
DELETE 0

francs=> select * From test_rule where id=1;  
id | name  
----+------  
 1 | a  
(1 row)

备注：果然禁止了表 test_rule 上的 delete 操作。

上面是通过在表创建规则的方法，实现禁止表上更新和删除操作，关于创建规则的语法可参考本文末尾。接下来看下另外一种方法，

方法二: 创建触发器

2.1 创建测试表

francs=> create table test_trigger(id int4 primary key ,name varchar(32));  
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_trigger_pkey" for table "test_trigger"  
CREATE TABLE

francs=> insert into test_trigger select generate_series(1,10),'b';  
INSERT 0 10

francs=> select * from test_trigger;  
id | name  
----+------  
 1 | b  
 2 | b  
 3 | b  
 4 | b  
 5 | b  
 6 | b  
 7 | b  
 8 | b  
 9 | b  
10 | b  
(10 rows)

2.2 创建触发器函数

CREATE OR REPLACE FUNCTION func_test_trigger()  
RETURNS trigger  
LANGUAGE plpgsql  
AS $function$  
BEGIN  
 RAISE EXCEPTION 'Attention: can not update or delete table test_trigger,Please contact francs !';  
END;  
$function$;

2.3 创建 UPDATE 和 DELETE 触发器

create trigger trigger_test_trigger_update BEFORE UPDATE ON test_trigger FOR EACH ROW EXECUTE PROCEDURE func_test_trigger();
  
create trigger trigger_test_trigger_delete BEFORE DELETE ON test_trigger FOR EACH ROW EXECUTE PROCEDURE func_test_trigger();

francs=> /d test_trigger  
 Table "francs.test_trigger"  
Column | Type | Modifiers  
--------+-----------------------+-----------  
id | integer | not null  
name | character varying(32) |  
Indexes:  
 "test_trigger_pkey" PRIMARY KEY, btree (id)  
Triggers:  
 trigger_test_trigger_delete BEFORE DELETE ON test_trigger FOR EACH ROW EXECUTE PROCEDURE func_test_trigger()  
 trigger_test_trigger_update BEFORE UPDATE ON test_trigger FOR EACH ROW EXECUTE PROCEDURE func_test_trigger()

备注：在表 test_trigger 上创建 update 和 delete 两个触发器。

2.4 update 测试

francs=> select * from test_trigger where id=1;  
id | name  
----+------  
 1 | b  
(1 row)

francs=> update test_trigger set name='bbb' where id=1;  
ERROR: Attention: can not update or delete table test_trigger,Please contact francs !  

francs=> select * from test_trigger where id=1;  
id | name  
----+------  
 1 | b  
(1 row)

2.5 delete 测试

francs=> select * from test_trigger where id=1;  
id | name  
----+------  
 1 | b  
(1 row)  
  
francs=> delete from test_trigger where id=1;  
ERROR: Attention: can not update or delete table test_trigger,Please contact francs !

francs=> select * from test_trigger where id=1;  
id | name  
----+------  
 1 | b  
(1 row)

备注：在表 test_trigger 上创建了 update/delete trigger 后，之后再去 update 或者 delete 表数据，将抛出 ERROR。

参考

附一 CREATE RULE

Name
CREATE RULE – define a new rewrite rule
Synopsis
CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command … ) }

附二 CREATE TRIGGER

Name
CREATE TRIGGER – define a new trigger
Synopsis
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR … ] }
ON table
[ FROM referenced_table_name ]
{ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE PROCEDURE function_name ( arguments )
where event can be one of:
INSERT
UPDATE [ OF column_name [, … ] ]
DELETE
TRUNCATE

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/237892.html

PostgreSQL: 禁止表上数据更新或删除的方法

方法一: 创建规则

方法二: 创建触发器

参考

附一 CREATE RULE

附二 CREATE TRIGGER

发表回复

PostgreSQL: 禁止表上数据更新或删除的方法

方法一: 创建规则

方法二: 创建触发器

参考

附一 CREATE RULE

附二 CREATE TRIGGER

相关推荐

发表回复