Re: Listing Triggers

On Wed, 30 Jan 2002, Brian McCane wrote:

> 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.

If you're using something below (I think) 7.1.3, pg_dump doesn't dump the
information on the other table in the foreign key so the info is lost
after a dump/restore sequence. I was pretty sure this was fixed in 7.1.3
though.

> 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'?

IIRC you need to use the trigger name and not the constraint name for drop
trigger, so if you can find the rows, you should be able to do
DROP TRIGGER "RI_..." ON foo; (the double quotes are required around the
trigger name.