PostgreSQL トリガーの有効化・無効化

2020年4月17日

ALTER TABLEでトリガーを有効化、無効化する

以下のSQLでトリガーを有効化、無効化することができる。

-- トリガー名を指定して無効化
alter table {テーブル名} disable trigger {トリガー名};

-- テーブルの全トリガーを無効化
alter table {テーブル名} disable trigger all;

-- トリガー名を指定して有効化
alter table {テーブル名} enable trigger {トリガー名};

-- テーブルの全トリガーを有効化
alter table {テーブル名} enable trigger all;

以下のテーブルのトリガーを無効化する

TestDB=# \d customer
                          Table "public.customer"
    Column     |           Type           | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
 id            | integer                  |           | not null |
 tenant_name   | text                     |           |          |
 customer_name | text                     |           |          |
 customer_div  | integer                  |           |          |
 birth_day     | timestamp with time zone |           |          |
Indexes:
    "customer_pkey" PRIMARY KEY, btree (id)
    "index_customer_birthday" btree (birth_day)
Policies:
    POLICY "tenant_policy"
      USING ((tenant_name = split_part((CURRENT_USER)::text, '_'::text, 1)))
Triggers:
    insert_tenant_name BEFORE INSERT ON customer FOR EACH ROW EXECUTE PROCEDURE insert_tenant_name()

TestDB=# alter table customer disable trigger insert_tenant_name ;
ALTER TABLE
TestDB=# \d customer
                          Table "public.customer"
    Column     |           Type           | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
 id            | integer                  |           | not null |
 tenant_name   | text                     |           |          |
 customer_name | text                     |           |          |
 customer_div  | integer                  |           |          |
 birth_day     | timestamp with time zone |           |          |
Indexes:
    "customer_pkey" PRIMARY KEY, btree (id)
    "index_customer_birthday" btree (birth_day)
Policies:
    POLICY "tenant_policy"
      USING ((tenant_name = split_part((CURRENT_USER)::text, '_'::text, 1)))
Disabled user triggers:
    insert_tenant_name BEFORE INSERT ON customer FOR EACH ROW EXECUTE PROCEDURE insert_tenant_name()

トリガーを有効化する。

TestDB=# alter table customer enable trigger insert_tenant_name ;
ALTER TABLE
TestDB=# \d customer
                          Table "public.customer"
    Column     |           Type           | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
 id            | integer                  |           | not null |
 tenant_name   | text                     |           |          |
 customer_name | text                     |           |          |
 customer_div  | integer                  |           |          |
 birth_day     | timestamp with time zone |           |          |
Indexes:
    "customer_pkey" PRIMARY KEY, btree (id)
    "index_customer_birthday" btree (birth_day)
Policies:
    POLICY "tenant_policy"
      USING ((tenant_name = split_part((CURRENT_USER)::text, '_'::text, 1)))
Triggers:
    insert_tenant_name BEFORE INSERT ON customer FOR EACH ROW EXECUTE PROCEDURE insert_tenant_name()