Drop ALL triggers from Postgres DB?

Question

Is there any way to drop ALL triggers from ALL tables in Postgres? I know there's a pg_trigger table I could look at, but it doesn't look like it contains enough information for me to decipher which triggers I have added to my tables.

It also looks like Foreign Key constraints show up in the pg_trigger table, which I DO NOT want to drop. I just want to drop the user created trigger from my tables and keep the FKs.

Any suggestions?

Answer 1

Thanks, James.

The function from Drop ALL triggers from Postgres DB? strips only the occurrence from the first table and leaves the triggers with the same name in other tables. Here is the fixed function:

CREATE OR REPLACE FUNCTION strip_all_triggers() RETURNS text AS $$ DECLARE
    triggNameRecord RECORD;
    triggTableRecord RECORD;
BEGIN
    FOR triggNameRecord IN select distinct(trigger_name) from information_schema.triggers where trigger_schema = 'public' LOOP
        FOR triggTableRecord IN SELECT distinct(event_object_table) from information_schema.triggers where trigger_name = triggNameRecord.trigger_name LOOP
            RAISE NOTICE 'Dropping trigger: % on table: %', triggNameRecord.trigger_name, triggTableRecord.event_object_table;
            EXECUTE 'DROP TRIGGER ' || triggNameRecord.trigger_name || ' ON ' || triggTableRecord.event_object_table || ';';
        END LOOP;
    END LOOP;

    RETURN 'done';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

select strip_all_triggers();

Answer 2

I prefer this (based on that) over the accepted answer by @kuznetso3v because it gives me a chance to inspect the DROP STATEMENTs before executing them with copy-paste:

SELECT 'DROP TRIGGER ' || trigger_name || ' ON ' || event_object_table || ';'
FROM information_schema.triggers
WHERE trigger_schema = 'public';

Answer 3

Take a look in the information_schema:

SELECT * FROM information_schema.triggers;

Answer 4

Simply cascade drop the language in which you created the triggers.
For example, I create triggers in plpgsql, so the following query deletes all triggers instantaneously -

DROP LANGUAGE plpgsql CASCADE;

Answer 5

UPDATE: See the real solution for the full function you want.

Alright, I came up with a function that does this for me:

CREATE OR REPLACE FUNCTION strip_all_triggers() RETURNS text AS $$ DECLARE
        triggNameRecord RECORD;
    triggTableRecord RECORD;
BEGIN
    FOR triggNameRecord IN select distinct(trigger_name) from information_schema.triggers where trigger_schema = 'public' LOOP
        SELECT distinct(event_object_table) INTO triggTableRecord from information_schema.triggers where trigger_name = triggNameRecord.trigger_name;
        RAISE NOTICE 'Dropping trigger: % on table: %', triggNameRecord.trigger_name, triggTableRecord.event_object_table;
        EXECUTE 'DROP TRIGGER ' || triggNameRecord.trigger_name || ' ON ' || triggTableRecord.event_object_table || ';';
    END LOOP;

    RETURN 'done';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

select strip_all_triggers();

That will drop any trigger in your public schema.

Answer 6

Easiest will be to pg_dump -s object definitions and filter it for lines starting with CREATE TRIGGER.

Something like

./pg_dump -s db_name | grep '^CREATE TRIGGER' | \
  while read _ _ triggername _; do \
    echo drop trigger "$triggername;"; \
  done

(in bash) should work (review it and then run in database).

But perhaps you should consider alter table table_name disable trigger trigger_name instead.

Answer 7

You could start from this query, to find outr trigger names:

select * from pg_trigger t,pg_proc where
 pg_proc.oid=t.tgfoid

Answer 8

The top answer is still flawed because there is no need for two loops.

It can be done by:

CREATE PROCEDURE _DropTableTriggers()
AS
$$
DECLARE
    _rec    RECORD;
BEGIN
    FOR _rec IN
        SELECT  DISTINCT    event_object_table, trigger_name
        FROM    INFORMATION_SCHEMA.triggers
    LOOP
        EXECUTE 'DROP TRIGGER ' || _rec.trigger_name || ' ON ' || _rec.event_object_table || ';';
    END LOOP;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

Answer 9

I made two improvements of the solution from the older answers:

• added filtering by trigger name, table and schema
• properly handling "truncate" triggers (ignored by the original solution)

---

CREATE OR REPLACE FUNCTION strip_all_triggers() RETURNS text AS $$ DECLARE
    triggRecord RECORD;
BEGIN
    create temp table all_triggers on commit drop as (
        SELECT tgname AS trigger_name, n.nspname as trigger_schema,  relname as trigger_table
        FROM pg_trigger
                 JOIN pg_class ON pg_class.oid = tgrelid
                 JOIN pg_namespace n ON n.oid = pg_class.relnamespace);

    FOR triggRecord IN select distinct on (trigger_schema, trigger_table, trigger_name) trigger_schema, trigger_table, trigger_name from all_triggers
                       where trigger_schema like 'public' and trigger_name like '%' -- MY FILTER
        LOOP
            RAISE NOTICE 'Dropping trigger: % on table: %.%', triggRecord.trigger_name, triggRecord.trigger_schema, triggRecord.trigger_table;
            EXECUTE 'DROP TRIGGER ' || triggRecord.trigger_name || ' ON ' || triggRecord.trigger_schema || '.' || triggRecord.trigger_table || ';';
        END LOOP;

    RETURN 'done';
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

select strip_all_triggers();

Answer 10

Following on Drux's response (Thanks!), I added the 'distinct' keyword to eliminate double statements that break bulk query runs.

SELECT distinct 'DROP TRIGGER ' || trigger_name || ' ON ' || event_object_table || ';'
FROM information_schema.triggers
WHERE trigger_schema = 'public';