如何使用PL/pgSQL触发器限制删除?

问题描述：

如果客户端用户试图从一个表中删除5条以上的记录，我想使用触发器来限制它.我有一个基本的想法可以做到这一点，但是我不知道如何实现这个想法.感谢您的帮助.

If the client user is trying to delete more than 5 records from a Table i want to restrict that using a trigger. I have a basic idea to do that but i don't know how to implement the Idea. I appreciate any HELP.

基本思想:在触发器IF TG_OP =删除且要删除的记录数大于5的情况下，然后选择限制".

Basic Idea : In Trigger IF TG_OP = Delete and the count of records to be deleted are more than 5 then Restrict.

CREATE TRIGGER adjust_count_trigger BEFORE DELETE ON schemaname.tablename
FOR EACH ROW EXECUTE PROCEDURE public.adjust_count();

CREATE OR REPLACE FUNCTION adjust_count()
RETURNS TRIGGER AS
$$
    DECLARE
        num_rows int;
        num_rows1 int;

    BEGIN

        IF TG_OP = 'DELETE' THEN

            EXECUTE 'select count(*) from '||TG_TABLE_SCHEMA ||'.'||TG_RELNAME ||' where oid = old.oid ' into num_rows  ;


            IF num_rows > 5   Then

            RAISE NOTICE 'Cannot Delete More than 5 Records , % ', num_rows ;

            END IF ;


        END IF ;

        RETURN OLD;
    END;
$$
LANGUAGE 'plpgsql';

答

在早期版本的Postgres中，您可以模拟Postgres 10中引入的转换表.您需要两个触发器.

In earlier versions of Postgres you can simulate a transition table introduced in Postgres 10. You need two triggers.

create trigger before_delete 
before delete on my_table
for each row execute procedure before_delete();

create trigger after_delete 
after delete on my_table
for each statement execute procedure after_delete();

在第一个触发器中，创建一个临时表并在其中插入一行:

In the first trigger create a temp table and insert a row into it:

create or replace function before_delete()
returns trigger language plpgsql as $$
begin
    create temp table if not exists deleted_rows_of_my_table (dummy int);
    insert into deleted_rows_of_my_table values (1);
    return old;
end $$;

在临时表的其他触发器计数行中并将其删除:

In the other trigger count rows of the temp table and drop it:

create or replace function after_delete()
returns trigger language plpgsql as $$
declare
    num_rows bigint;
begin
    select count(*) from deleted_rows_of_my_table into num_rows;
    drop table deleted_rows_of_my_table;
    if num_rows > 5 then
        raise exception 'Cannot Delete More than 5 Records , % ', num_rows;
    end if;
    return null;
end $$;

上述解决方案似乎有点怪异，但如果删除之前仅临时表不存在，则是安全的(不要对多个表使用相同的临时表名称).

The above solution may seem a bit hacky but it is safe if only the temp table does not exist before delete (do not use the same name of the temp table for multiple tables).

在rextester中对其进行测试.

如何使用PL/pgSQL触发器限制删除?

相关推荐