Thread: listing triggers

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

> 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

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)
listtables/indexes/sequences/views/system tables
 

No triggers....

So, I am still in the dark.

Regards,
Erwin Moller

> 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

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                  |
notnullname               | character varying(250)   | not nullns_product_type_id | integer                  | not
nulldisplay           | boolean                  | not null default falsedate_added         | timestamp with time zone
|not nulllast_update        | timestamp with time zone | not nullactive             | boolean                  | not
nulldefault 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
DELETEON 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

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)