[PostgreSQL] 트리거 조회

--foreign key 걸린테이블 목록확인

SELECT kcu.table_name AS child_table,

kcu.table_schema AS child_schema,

    kcu.column_name AS child_column,

    kcu.constraint_name AS child_constraint

 FROM information_schema.table_constraints tc

   JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name

   JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name

WHERE tc.constraint_type = 'FOREIGN KEY'

  and tc.constraint_schema = 'naqsifh'

 and ccu.table_name = 'tn_menu_atchmnfl_m'

GROUP BY kcu.table_name, kcu.table_schema, kcu.column_name, kcu.constraint_name;

 

 

select * from information_schema.table_constraints

 

select * from information_schema.constraint_column_usage

 

select * from information_schema.key_column_usage

 

 

--현재 테이블이 참조하고 있는 테이블(foreign key의 주인) 및 컬럼 확인

 SELECT tc.constraint_type,

      tc.table_name AS child_table,

    kcu.column_name AS child_column,

    ccu.table_name AS foreign_table,

    ccu.column_name AS foreign_column

 FROM information_schema.table_constraints tc

  JOIN information_schema.key_column_usage kcu ON tc.constraint_name::text = kcu.constraint_name::text

   JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name::text = tc.constraint_name::text

  WHERE 1 = 1

   and tc.constraint_type = 'FOREIGN KEY'

   and tc.constraint_schema = 'naqsifh'

   and tc.table_name = 'th_menu_atchmnfl_m';

 

 

/* 트리거(insert,update,delete) 상태조회

 SELECT trg.tgname as tigger_name,

                CASE trg.tgtype::INTEGER & 66

                    WHEN 2 THEN 'BEFORE'

                    WHEN 64 THEN 'INSTEAD OF'

                    ELSE 'AFTER'

                END AS trigger_type,

               CASE trg.tgtype::INTEGER & cast(28 AS INT2)

                 WHEN 16 THEN 'UPDATE'

                 WHEN 8 THEN 'DELETE'

                 WHEN 4 THEN 'INSERT'

                 WHEN 20 THEN 'INSERT, UPDATE'

                 WHEN 28 THEN 'INSERT, UPDATE, DELETE'

                 WHEN 24 THEN 'UPDATE, DELETE'

                 WHEN 12 THEN 'INSERT, DELETE'

               END AS trigger_event,

               ns.nspname||'.'||tbl.relname AS trigger_table,

               obj_description(trg.oid) AS remarks,

                 CASE

                  WHEN trg.tgenabled='O' THEN 'ENABLED'

                    ELSE 'DISABLED'

                END AS status,

                CASE trg.tgtype::INTEGER & 1

                  WHEN 1 THEN 'ROW'::TEXT

                  ELSE 'STATEMENT'::TEXT

                END AS trigger_level,

                n.nspname || '.' || proc.proname AS function_name

        FROM pg_trigger trg

         JOIN pg_proc proc ON proc.oid = trg.tgfoid

         JOIN pg_catalog.pg_namespace n ON n.oid = proc.pronamespace

         JOIN pg_class tbl ON trg.tgrelid = tbl.oid

         JOIN pg_namespace ns ON ns.oid = tbl.relnamespace

        WHERE

          trg.tgname not like 'RI_ConstraintTrigger%'

          AND trg.tgname not like 'pg_sync_pg%';