postgresql - 如何使用 BEFORE 触发器防止在 PostgreSQL 中的继承表上插入、更新和删除

Question

当使用表继承时，我想强制对后代表执行插入、更新和删除语句。我认为一种简单的方法是使用这样的触发函数:

CREATE FUNCTION test.prevent_action() RETURNS trigger AS $prevent_action$
    BEGIN
        RAISE EXCEPTION
            '% on % is not allowed. Perform % on descendant tables only.',
            TG_OP, TG_TABLE_NAME, TG_OP;
    END;
$prevent_action$ LANGUAGE plpgsql;

...我将从使用 BEFORE INSERT OR UPDATE OR DELETE 指定的触发器中引用。

这似乎适用于插入，但不适用于更新和删除。

以下测试序列展示了我观察到的内容:

DROP SCHEMA IF EXISTS test CASCADE;
psql:simple.sql:1: NOTICE:  schema "test" does not exist, skipping
DROP SCHEMA
CREATE SCHEMA test;
CREATE SCHEMA
-- A function to prevent anything
-- Used for tables that are meant to be inherited
CREATE FUNCTION test.prevent_action() RETURNS trigger AS $prevent_action$
    BEGIN
        RAISE EXCEPTION
            '% on % is not allowed. Perform % on descendant tables only.',
            TG_OP, TG_TABLE_NAME, TG_OP;
    END;
$prevent_action$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TABLE test.people (
    person_id SERIAL PRIMARY KEY,
    last_name text,
    first_name text
);
psql:simple.sql:17: NOTICE:  CREATE TABLE will create implicit sequence "people_person_id_seq" for serial column "people.person_id"
psql:simple.sql:17: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "people_pkey" for table "people"
CREATE TABLE
CREATE TRIGGER prevent_action BEFORE INSERT OR UPDATE OR DELETE ON test.people
    FOR EACH ROW EXECUTE PROCEDURE test.prevent_action();
CREATE TRIGGER
CREATE TABLE test.students (
    student_id SERIAL PRIMARY KEY
) INHERITS (test.people);
psql:simple.sql:24: NOTICE:  CREATE TABLE will create implicit sequence "students_student_id_seq" for serial column "students.student_id"
psql:simple.sql:24: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "students_pkey" for table "students"
CREATE TABLE
--The trigger successfully prevents this INSERT from happening
--INSERT INTO test.people (last_name, first_name) values ('Smith', 'Helen');
INSERT INTO test.students (last_name, first_name) values ('Smith', 'Helen');
INSERT 0 1
INSERT INTO test.students (last_name, first_name) values ('Anderson', 'Niles');
INSERT 0 1
UPDATE test.people set first_name = 'Oh', last_name = 'Noes!';
UPDATE 2
SELECT student_id, person_id, first_name, last_name from test.students;
 student_id | person_id | first_name | last_name 
------------+-----------+------------+-----------
          1 |         1 | Oh         | Noes!
          2 |         2 | Oh         | Noes!
(2 rows)

DELETE FROM test.people;
DELETE 2
SELECT student_id, person_id, first_name, last_name from test.students;
 student_id | person_id | first_name | last_name 
------------+-----------+------------+-----------
(0 rows)

所以我想知道我做错了什么，在这个例子中允许直接针对 test.people 表进行更新和删除。

Answer 1

触发器设置为执行FOR EACH ROW，但是test.people 中没有行，这就是它没有运行的原因。

作为旁注，您可以发出 select * from ONLY test.people 以列出 test.people 中不属于子表的行。

解决方案似乎很简单:设置触发器 FOR EACH STATEMENT 而不是 FOR EACH ROW，因为无论如何你都想禁止整个语句。

CREATE TRIGGER prevent_action BEFORE INSERT OR UPDATE OR DELETE ON test.people
    FOR EACH STATEMENT EXECUTE PROCEDURE test.prevent_action();