注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

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

2012-08-10 15:30:10|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |


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

 

一 方法一:使用 rule 创建规则 

     在 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 规则

 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。
    

三 参考
http://www.postgresql.org/docs/9.2/static/sql-createrule.html
http://www.postgresql.org/docs/9.2/static/sql-createtrigger.html


--附一 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   
   

  评论这张
 
阅读(21643)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016