Re: listing triggers

On Sunday 08 August 2004 05:05, Erwin Moller wrote:
> >> But how can I get a listing of all used triggers on a certain table?
> >
> > \d <tablename>
> >
> Thanks for your response, but this is what I get:
>
> column, Type, and Modifiers
> +
> Indexes and foreign key contraints.
> No triggers.
>
> The triggers are responsible for checking FK-contraints in other tables,
> that use the table I want to list as refering key.
>

It depends on how you've implmented your FK's... for example

live=# \d ns_category
Table "public.ns_category"
Column | Type | Modifiers
--------------------+--------------------------+------------------------
ns_category_id | integer | not null
name | character varying(250) | not null
ns_product_type_id | integer | not null
display | boolean | not null default false
date_added | timestamp with time zone | not null
last_update | timestamp with time zone | not null
active | boolean | not null default false
Indexes:
"ns_category_id_pkey" primary key, btree (ns_category_id)
Triggers:
"RI_ConstraintTrigger_18883782" AFTER INSERT OR UPDATE ON ns_category FROM
ns_product_type NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins"('ns_product_type_id_fk', 'ns_category',
'ns_product_type', 'UNSPECIFIED', 'ns_product_type_id', 'ns_product_type_id')
"RI_ConstraintTrigger_18883789" AFTER DELETE ON ns_category FROM
ns_product NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_del"('ns_category_id_fk', 'ns_product', 'ns_category',
'UNSPECIFIED', 'ns_category_id', 'ns_category_id')
"RI_ConstraintTrigger_18883790" AFTER UPDATE ON ns_category FROM
ns_product NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_upd"('ns_category_id_fk', 'ns_product', 'ns_category',
'UNSPECIFIED', 'ns_category_id', 'ns_category_id')

If you are using explicit triggers for FK they will show up under psql \d
output, however if you use implicit triggers (dervied from references syntax
in create table, or alter table add foriegn key syntax) then you wont see the
"triggers", but the constraints will "do the right thing".

db_customer=# create table test (alunos_id integer references alunos (id) on
delete cascade);
CREATE TABLE
db_customer=# \d test
Table "public.test"
Column | Type | Modifiers
-----------+---------+-----------
alunos_id | integer |
Foreign-key constraints:
"$1" FOREIGN KEY (alunos_id) REFERENCES alunos(id) ON DELETE CASCADE

--
Robert Treat
Build A Better Lamp :: Linux Apache {middleware} PostgreSQL