Re: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Wang, Mary Y" <mary(dot)y(dot)wang(at)boeing(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
Date: 2010-02-11 02:30:11
Message-ID: 25229.1265855411@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Wang, Mary Y" <mary(dot)y(dot)wang(at)boeing(dot)com> writes:
> Here is my complicated problem. I tried to delete a user from my users table, but it said
> "ERROR: bug_assigned_to_fk referential integrity violation - key in users still referenced from bug"
> Ok.
> Then I saw this statement in the .sql file.
> "CREATE CONSTRAINT TRIGGER "bug_assigned_to_fk" AFTER DELETE ON "users" FROM "bug" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('bug_assigned_to_fk', 'bug', 'users', 'FULL', 'assigned_to', 'user_id');"
> Then I used this command to delete the constraint trigger:
> " drop trigger bug_assign_to_fk on bug;"
> I received error:
> "ERROR: DropTrigger: there is no trigger bug_assign_to_fk on relation bug"

It looks to me like you misspelled the trigger name --- what you
quote there is bug_assigned_to_fk not bug_assign_to_fk. Also, the
trigger is attached to table users not table bug.

> Here is the bug table. Please NOTE there is no constraint listed in the bug table.

I think in 7.1 that trigger would be shown as a trigger if you did \d users,
but it's not going to be mentioned by \d bug.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Wang, Mary Y 2010-02-11 03:07:08 Re: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
Previous Message Koichi Suzuki 2010-02-11 01:59:51 Re: Problem with pg_compresslog'd archives