Thread: Listing Triggers corresponding to foreign keys

Hi all,

Am I right in saying that when you add a constraint to a table it just
produces three triggers?  If so which triggers?

There seems to be no easy way of listing triggers (or rules).  I assumed
there was something like \d? which would do the job but could find nothing
in the docs.  I can find a list in pg_trigger but am not sure of the
details.

BTW are there any docs for each of the system tables as there seems to be no
other way to certain things than dive into these and work what each does!!

It says in the docs to drop constraints on a table you drop the table and
remake it, but I only want to drop one of the constraints, do I just have to
drop the triggers (When I have found out which ones) or drop and remake the
table and then remake the other constraints.

Any help would be appreciated.

Ben

On Tue, 10 Oct 2000, Trewern, Ben wrote:

> Hi all,
>
> Am I right in saying that when you add a constraint to a table it just
> produces three triggers?  If so which triggers?

A foreign key constraint, yes.  (Check constraints, unique, primary key
and not null are handled differently).

> There seems to be no easy way of listing triggers (or rules).  I assumed
> there was something like \d? which would do the job but could find nothing
> in the docs.  I can find a list in pg_trigger but am not sure of the
> details.

Pretty much that is the way to find the information.
For foreign key constraints, there is one trigger on the foreign key
table, and two on the primary key table.

For the foreign key triggers, the name will be RI_Constraint_Trigger_<#>
(where I believe # is the oid of the trigger).  There will be a
tgconstrname with whatever name you gave the constraint (you did name it
right? :) )  If not, you can use the tgargs (arguments to the trigger)
to determine the correct one.  Basically it's:
<constraintname>\000<fktable>\000<pktable>\000<match type>\000
<col1fk>\000<col1pk>\000...<colnfk>\000<colnpk>\000

> It says in the docs to drop constraints on a table you drop the table and
> remake it, but I only want to drop one of the constraints, do I just have to
> drop the triggers (When I have found out which ones) or drop and remake the
> table and then remake the other constraints.

I believe once you've found them, you can probably get away with
DROP TRIGGER "RI_Constraint_Trigger_<#>" on <table>;  (Note the "" are
required)