pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedlist triggers ?
4+ messages / 2 participants
[nested] [flat]
* list triggers ?
@ 2002-01-21 09:03 Urs Steiner <postgresql@darkstone.ch>
2002-01-21 18:06 ` Re: list triggers ? Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Urs Steiner @ 2002-01-21 09:03 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: list triggers ?
2002-01-21 09:03 list triggers ? Urs Steiner <postgresql@darkstone.ch>
@ 2002-01-21 18:06 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-01-22 15:48 ` Re: list triggers ? Urs Steiner <postgresql@darkstone.ch>
0 siblings, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2002-01-21 18:06 UTC (permalink / raw)
To: Urs Steiner <postgresql@darkstone.ch>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: list triggers ?
2002-01-21 09:03 list triggers ? Urs Steiner <postgresql@darkstone.ch>
2002-01-21 18:06 ` Re: list triggers ? Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-01-22 15:48 ` Urs Steiner <postgresql@darkstone.ch>
2002-01-22 16:12 ` Re: list triggers ? Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Urs Steiner @ 2002-01-22 15:48 UTC (permalink / raw)
To: pgsql-sql
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 ?
TIA,
Urs
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: list triggers ?
2002-01-21 09:03 list triggers ? Urs Steiner <postgresql@darkstone.ch>
2002-01-21 18:06 ` Re: list triggers ? Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-01-22 15:48 ` Re: list triggers ? Urs Steiner <postgresql@darkstone.ch>
@ 2002-01-22 16:12 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2002-01-22 16:12 UTC (permalink / raw)
To: Urs Steiner <postgresql@darkstone.ch>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-01-22 16:12 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-21 09:03 list triggers ? Urs Steiner <postgresql@darkstone.ch>
2002-01-21 18:06 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-01-22 15:48 ` Urs Steiner <postgresql@darkstone.ch>
2002-01-22 16:12 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox