Обсуждение: list triggers ?
Hi all yesterday, i created the following table: create table kunden (id serial primary key, name varchar(30) not null, adresse1 varchar(30) not null, adresse2 varchar(30), plz varchar(10) not null, ort varchar(30) not null, legi varchar(10) not null, tel1 varchar(20), tel2 varchar(20), email varchar(60), rechnung boolean default False not null, login varchar(10) not null, password varchar(10) not null, abteilung integer not null, constraint kunden_abt_fk FOREIGN key(abteilung) references kategorien ); part of the output of this command was: NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE now, the creation is actually OK. But how do i find out which triggers exist and what they look like ? (generically, in this case i assume it to be some default, available fropm the docu) Thanks, Urs
On Mon, 21 Jan 2002, Urs Steiner wrote: > part of the output of this command was: > > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY > check(s) > CREATE > > now, the creation is actually OK. But how do i find out which triggers > exist and what they look like ? (generically, in this case i assume it > to be some default, available fropm the docu) It depends on what you want to see. select * from pg_trigger; will give you a list of the triggers. You'd need to join it with pg_class and pg_proc to get the table name and function name being called. Something like: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); If you want to see what the triggers are doing, in the case of the foreign key ones the code's in C in src/backend/utils/adt/ri_triggers.c I believe.
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\ 000kunden | 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 <trigger name> ON kunden;" but what is the trigger name ? tgname doesnt work, tgconstrname also not or am i entirely on the wrong track ? TIA, Urs
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 <trigger name> 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.