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)
