Re: Listing Triggers

On Wed, 30 Jan 2002, Arguile wrote:

> Brian McCane wrote:
> > How can I list the triggers on a specific table?
>
> Just as I was typing this I noticed Tom already replied. Just incase you
> want to know all triggers referencing the table in addition to those on it
> (eg. foreign key constraints).
>
> SELECT t.oid, t.*
> FROM   pg_trigger t, pg_class c
> WHERE  where c.oid in (t.tgrelid, t.tgconstrrelid)
>   AND  c.relname = 'foo';
>

    Thanks Tom and Arguile that definitely helps.  Now for my more
pressing, but forgotten question.  Shouldn't PostgreSQL have automatically
dropped that trigger when I dropped the table?  I dropped and recreated a
small table with a Foreign Key, and each time I do it, I get more triggers
sitting out there that reference the name of the table. This is probably
not a problem if it doesn't reference the correct oid, but I am not sure.
The other problem I had was that I didn't name the constraints that I
created, so I had to delete them from 'pg_trigger' manually, then I had to
update the trigger count in 'pg_class' manually.  Would it have worked if
I had changed the name from '<unnamed>' to 'killme' using an update to
'pg_trigger', and then said, "DROP TRIGGER killme ON foo'?

- brian

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"