Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0MGCRl90357 for ; Tue, 22 Jan 2002 11:12:27 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id BF046D5E5; Tue, 22 Jan 2002 08:12:31 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id B4A8F5BF4; Tue, 22 Jan 2002 08:12:31 -0800 (PST) Date: Tue, 22 Jan 2002 08:12:31 -0800 (PST) From: Stephan Szabo To: Urs Steiner Cc: Subject: Re: list triggers ? In-Reply-To: <0dfc01c1a35c$4c78fa00$ecb88481@ethz.ch> Message-ID: <20020122081058.N5946-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200201/256 X-Sequence-Number: 6340 On Tue, 22 Jan 2002, Urs Steiner wrote: > Hi Stephan > > > >> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY > >> check(s) > >> CREATE > > > select relname, tgname, tgtype, proname, prosrc, tgisconstraint, > > tgconstrname, tgconstrrelid, tgdeferrable, tginitdeferred, tgnargs, > > tgattr, tgargs from (pg_trigger join pg_class on tgrelid=pg_class.oid) > > join pg_proc on (tgfoid=pg_proc.oid); > > ok, if fone that and i could even modify the statement (where > relname='kunden'), so i get only the ones i want to see ... > > relname | tgname | tgtype | proname | > prosrc | tgisconstraint | tgconstrname | tgconstrrelid | > tgdeferrable | tginitdeferred | tgnargs | tgattr | > tgargs > ---------+----------------------------+--------+----------------------+- > ---------------------+----------------+-----------------------+--------- > ------+--------------+----------------+---------+--------+-------------- > --------------------------------------------------------------------- > kunden | RI_ConstraintTrigger_19083 | 21 | RI_FKey_check_ins | > RI_FKey_check_ins | t | kunden_abt_fk | > 18972 | f | f | 6 | | > kunden_abt_fk\000kunden\000kategorien\000UNSPECIFIED\000abteilung\000id\ > 000 > kunden | RI_ConstraintTrigger_19290 | 9 | RI_FKey_noaction_del | > RI_FKey_noaction_del | t | mitarbeiter_kunden_fk | > 19268 | f | f | 6 | | > mitarbeiter_kunden_fk\000mitarbeiter\000kunden\000UNSPECIFIED\000id\000i > d\000 > [and some more] > > as much as i get it, i want the first one, and i want to delete it > (erronous construction) ... > deleting is done with "DROP TRIGGER ON kunden;" > but what is the trigger name ? tgname doesnt work, tgconstrname also not > or am i entirely on the wrong track ? tgname is the right thing, although you'll need to double quote the name because it's mixed case. However, there are also two triggers on the target (I guess kunden_abt_fk?) that are associated with the constraint (each fk cosntraint is three triggers). I usually use the tgargs to determine which triggers go together.