注册

postgreSQL触发器常用SQL

相关链接 :

2021-05-09 添加评论
分享

没有找到相关结果

    已邀请:

    nccloud

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

    方法一: 创建规则

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

    1.1 创建测试表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    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 测试

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    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 规则

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    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 测试

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    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 创建测试表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    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 创建触发器函数

    1
    2
    3
    4
    5
    6
    7
    8
    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 触发器

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    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 测试

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    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 测试

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    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

    2021-05-09 0 0
    分享

    nccloud

    创建附件修改触发器


    1、建函数


    CREATE OR REPLACE FUNCTION func_no_delete_trigger()  
    
RETURNS trigger  
    
LANGUAGE plpgsql  
    
AS $function$  
    
BEGIN  
    
 RAISE EXCEPTION 'Attention: can not delete table bap_fs_header , Please contact nccloud@yonyou.com !';  
    END;  
    
$function$;


    2、建触发器

    CREATE TRIGGER "no_delete_trigger"
     BEFORE DELETE ON bap_fs_header
     FOR EACH ROW
     EXECUTE PROCEDURE func_no_delete_trigger()

    3、激活触发器

    ALTER TABLE bap_fs_headerENABLE TRIGGER "no_delete_trigger"



    2021-07-02 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    Copyright © 2025