How to find triggers in a PostgreSQL schema

Execute the SQL below as a user with ownership to the schema. To verify that you have schema privileges, execute:

SELECT has_schema_privilege('myuser', 'myschema', 'USAGE');

If the reply is

has_schema_privilege
----------------------
 t

You can login

psql -h localhost -d mydb -U myuser

and execute

\x
Expanded display is on.

SELECT
    'TRIGGER' AS object_type,
    t.tgname AS trigger_name,
    n.nspname AS schema_name,
    c.relname AS table_name,

    -- Decode tgenabled letters
    CASE t.tgenabled
        WHEN 'O' THEN 'ENABLE'
        WHEN 'A' THEN 'ENABLE ALWAYS'
        WHEN 'R' THEN 'ENABLE REPLICA'
        WHEN 'D' THEN 'DISABLED'
        ELSE t.tgenabled
    END AS enabled_state,

    -- Trigger timing from tgtype
    CASE
        WHEN (t.tgtype & 2)  <> 0 THEN 'BEFORE'
        WHEN (t.tgtype & 64) <> 0 THEN 'INSTEAD OF'
        ELSE 'AFTER'
    END AS timing,

    -- Events
    (SELECT string_agg(event, ', ')
     FROM (
            SELECT unnest(
                ARRAY[
                    CASE WHEN (t.tgtype & 4)  <> 0 THEN 'INSERT'   END,
                    CASE WHEN (t.tgtype & 8)  <> 0 THEN 'DELETE'   END,
                    CASE WHEN (t.tgtype & 16) <> 0 THEN 'UPDATE'   END,
                    CASE WHEN (t.tgtype & 32) <> 0 THEN 'TRUNCATE' END
                ]
            ) AS event
        ) ev
    ) AS events,

    pron.nspname || '.' || p.proname AS trigger_function

FROM pg_trigger t
JOIN pg_class c        ON c.oid = t.tgrelid
JOIN pg_namespace n    ON n.oid = c.relnamespace
JOIN pg_proc p         ON p.oid = t.tgfoid
JOIN pg_namespace pron ON pron.oid = p.pronamespace

WHERE n.nspname = current_schema()
  AND NOT t.tgisinternal
  AND t.tgconstraint = 0
  AND c.relkind <> 'p'
ORDER BY trigger_name;

Which will list your triggers:

-[ RECORD 1 ]----+------------------------------------------------------------------
object_type      | TRIGGER
trigger_name     | mytrg1
schema_name      | myschema
table_name       | mytable1
enabled_state    | E
timing           | BEFORE
events           | UPDATE
trigger_function | myschema.trigger_fct_upd_mytable1
-[ RECORD 2 ]----+------------------------------------------------------------------
object_type      | TRIGGER
trigger_name     | mytrg2
schema_name      | myschema
table_name       | mytable2
enabled_state    | E
timing           | AFTER
events           | DELETE
trigger_function | myschema.trg_close_fct

Useful query against v$archived_log in recovery situations

SELECT thread#,
         COUNT(*)                        AS cnt,
         MIN(sequence#)                  AS min_seq,
         MAX(sequence#)                  AS max_seq,
         MIN(first_time)                 AS min_time,
         MAX(next_time)                  AS max_time
  FROM   v$archived_log
  WHERE  deleted = 'NO'
  GROUP  BY thread#
  ORDER  BY thread#;

   THREAD#    CNT    MIN_SEQ    MAX_SEQ               MIN_TIME               MAX_TIME
__________ ______ __________ __________ ______________________ ______________________
         1     80       7936       8015 2026-02-16 15:38:02    2026-02-18 14:15:42

Dropping an audit policy for a specific user

You have the following audit policy:

CREATE AUDIT POLICY monitor_sys_everything
  ACTIONS ALL;

AUDIT POLICY monitor_sys_everything BY SYS;

I wanted to drop the auditing policy:

SQL> noaudit policy monitor_sys_everything;

Noaudit succeeded.

SQL> drop audit policy monitor_Sys_everything;
drop audit policy monitor_Sys_everything
*
ERROR at line 1:
ORA-46361: Audit policy cannot be dropped as it is currently enabled.

Reason:

The policy is enabled for a specific user. The "noaudit" statement used above will disable it for everyone.

Solution:

Disable it for the specific user:

SQL> NOAUDIT POLICY monitor_sys_everything BY SYS;

It can now be dropped:

drop audit policy monitor_sys_everything;