SQL 如何获取与 PostgreSQL 中的视图或表关联的触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25202133/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the trigger(s) associated with a view or a table in PostgreSQL
提问by Saravanan
I have one requirement that I have to get the list of triggers associated to the given table/view.
Can anyone help me to find the triggers for a table in PostgreSQL?
我有一个要求，即必须获取与给定表/视图关联的触发器列表。
谁能帮我在 PostgreSQL 中找到表的触发器？
回答by Vivek S.
This will return all the details you want to know
这将返回您想知道的所有详细信息
select * from information_schema.triggers
or if you want to sort the results of a specific table then you can try
或者如果您想对特定表格的结果进行排序，那么您可以尝试
SELECT event_object_table
,trigger_name
,event_manipulation
,action_statement
,action_timing
FROM information_schema.triggers
WHERE event_object_table = 'tableName' -- Your table name comes here
ORDER BY event_object_table
,event_manipulation
the following will return table name that has trigger
以下将返回具有触发器的表名
select relname as table_with_trigger
from pg_class
where pg_class.oid in (
select tgrelid
from pg_trigger
)
回答by Erwin Brandstetter
The problem with the view information_schema.triggers(besides being slow) is, per documentation:
根据文档information_schema.triggers，视图的问题（除了速度慢）是：
The view triggers contains all triggers defined in the current database on tables and views that the current user owns or has some privilege other than
SELECTon.
视图触发器包含在当前数据库中定义的所有触发器，这些触发器针对当前用户拥有或具有除
SELECTon之外的某些特权的表和视图。
Meaning, you only get to see triggers you have appropriate privileges on.
意思是，您只能看到您拥有适当权限的触发器。
To see alltriggers for a table, look in the system catalog pg_trigger
要查看表的所有触发器，请查看系统目录pg_trigger
SELECT tgname
FROM pg_trigger
WHERE tgrelid = 'myschema.mytbl'::regclass; -- optionally schema-qualified
Works for tables andviews.
Or you could use a GUI like pgAdmin that displays the list under the table node in the object browser.
适用于表和视图。
或者您可以使用像 pgAdmin 这样的 GUI，它在对象浏览器中的表节点下显示列表。
回答by Touko
On psql command-line tool you can also use \dS <table_name>(from https://serverfault.com/questions/331024/how-can-i-show-the-content-of-a-trigger-with-psql)
在 psql 命令行工具上，您还可以使用\dS <table_name>（来自https://serverfault.com/questions/331024/how-can-i-show-the-content-of-a-trigger-with-psql）
回答by Chanakya
select tgname
,relname
,tgenabled
,nspname from pg_trigger
join pg_class on (pg_class.oid=pg_trigger.tgrelid)
join pg_namespace on (nspowner=relowner);
tgenabled (To check if its disabled)
O = trigger fires in "origin" and "local" modes,
D = trigger is disabled,
R = trigger fires in "replica" mode,
A = trigger fires always.
回答by jeremyg
I noticed that infoschema does NOT contain key relation table information about triggers (at least in postgres 10). pg_triggers does contain this info. Also noticed that datagrip will not script relation tables when you script the triggers, so i'm assuming that it uses infoschema to script them (and then your table would be missing the relation tables, and the trigger functions referencing them would fail). PG documentation says that columns in infoschema for action_reference_old_table applies to a feature not available in postgres(10), but i'm definitely using them, and they definitely show up in pg_triggers. FYI.
我注意到 infoschema 不包含有关触发器的关键关系表信息（至少在 postgres 10 中）。pg_triggers 确实包含此信息。还注意到当您编写触发器脚本时，datagrip 不会编写关系表的脚本，因此我假设它使用 infoschema 来编写它们的脚本（然后您的表将缺少关系表，并且引用它们的触发器函数将失败）。PG 文档说，信息模式中 action_reference_old_table 的列适用于 postgres(10) 中不可用的功能，但我肯定在使用它们，并且它们肯定会出现在 pg_triggers 中。供参考。
