Hi,

When using psql I can list the tables and sequences by typing:
\d

futhermore:
\dt lists tables
\ds lists sequences
\d tablename lists that table.

etc. etc.

But how can I get a listing of all used triggers on a certain table?

Thanks for your time

Regards,
Erwin Moller

Thu, 05 Aug 2004 15:01:58 +0200
Erwin Moller
	<since_humans_read_this_I_am_spammed_too_much@spamyourself.com>
listing triggers
> futhermore:
> \dt lists tables
> \ds lists sequences
> \d tablename lists that table.
> 
> etc. etc.
> 
> But how can I get a listing of all used triggers on a certain table?

\d <tablename>

Chris


Sun, 08 Aug 2004 12:51:12 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: listing triggers
Christopher Kings-Lynne wrote:

>> futhermore:
>> \dt lists tables
>> \ds lists sequences
>> \d tablename lists that table.
>> 
>> etc. etc.
>> 
>> But how can I get a listing of all used triggers on a certain table?
> 
> \d <tablename>
> 
> Chris
> 

Hi Chris,

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.

This is what the help says:

 \d [NAME]      describe table, index, sequence, or view
 \d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
                list tables/indexes/sequences/views/system tables

No triggers....

So, I am still in the dark.

Regards,
Erwin Moller

Sun, 08 Aug 2004 11:05:53 +0200
Erwin Moller
	<since_humans_read_this_I_am_spammed_too_much@spamyourself.com>
Re: listing triggers
> Thanks for your response, but this is what I get:
> 
> column, Type, and Modifiers
> + 
> Indexes and foreign key contraints.
> No triggers.

It lists triggers.  Trust me, I wrote it.

> The triggers are responsible for checking FK-contraints in other tables, 
> that use the table I want to list as refering key.

OK, there you go.  Foreign key triggers are hidden from you 
automatically.  They're considered implementation specific information.

Chris

Mon, 09 Aug 2004 09:48:16 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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

Sun, 8 Aug 2004 22:04:05 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: listing triggers
Christopher Kings-Lynne wrote:

>> Thanks for your response, but this is what I get:
>> 
>> column, Type, and Modifiers
>> +
>> Indexes and foreign key contraints.
>> No triggers.
> 
> It lists triggers.  Trust me, I wrote it.

(Sorry for late response)
Hi Chris,

You wrote it?!?
Ok, in that case I'll better take your word for it. :-)

This is a perfect chance to compliment you for your contributions to that 
great database, so.. Thanks! I love it. It actually always works.

I never realized that there is a difference between triggers created 
implicitly and explicitly.

Robert Treat explained also that the reason that I don't see them has to do 
with the fact that the triggers were created implicitly (dervied from 
references syntax in create table, or alter table add foreign key syntax).

Thanks for your help/explanation. (Robert too!)

Regards,
Erwin Moller (a happy PostgreSQL end-user)




Thu, 19 Aug 2004 15:38:07 +0200
Erwin Moller
	<since_humans_read_this_I_am_spammed_too_much@spamyourself.com>
Re: listing triggers