On Sun, 14 Jul 2002, Andre Schlieper wrote: > Hi there, > I've some problems droping tiggers (foreign key-constraint) on > postgresql 7.1.1, > example-tables are like this : > > CREATE TABLE pers ( > pid INT NOT NULL PRIMARY KEY, > pname TEXT NOT NULL); > > CREATE TABLE tasks ( > taskid SERIAL NOT NULL PRIMARY KEY, > pid INT NOT NULL CONSTRAINT tasks__ref_p REFERENCES > pers, > task TEXT NOT NULL); > > This creates two simple tables, linked with one foreign-key > "tasks_ref_p". > This constraint is done with three triggers (2 on the pk table, 1 on the > fk table), wich are named: > RI_ConstraintTrigger_594265 > RI_ConstraintTrigger_594267 > RI_ConstraintTrigger_594263 > > > I tried to delete one of these triggers with > drop trigger "RI_ConstraintTrigger_594265" on pers; > that fails with > ERROR: DropTrigger: there is no trigger ri_constrainttrigger_594265 on > relation pers
The first one should be on tasks, so I think you may have been referencing the wrong table. > I also tried to delete these triggers with > DELETE FROM pg_trigger WHERE tgname='RI_ConstraintTrigger_594265'; > wich worked, all trigger are away from pg_trigger. Doing this is dangerous as you've noticed... The second error you got was because it couldn't find the appropriate number of triggers on the relation. > Q1 : How can I get DROP TRIGGER to work, why does it not work here ? > Q2: what's the right strategy to delete directly on pg_trigger ? was it > only luck that it is working that way; deleting on pg_triggers and > fixing on pg_class(reltriggers) ? Doing the delete/update will work (assuming that you correctly set the number of triggers as opposed to setting it to 0 unconditionally - I believe pg_dump in data only mode outputs a statement that will do that for you). If there are triggers and you set reltriggers to 0, it will work until you add a trigger at which point I think it starts failing again. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
