Re: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure? - Mailing list pgsql-general

Ok.  I typed the correct name this time, and got the same error.
"drop trigger bug_assigned_to_fk on users;
ERROR:  DropTrigger: there is no trigger bug_assigned_to_fk on relation users "
"drop trigger bug_assigned_to_fk on bug;
ERROR:  DropTrigger: there is no trigger bug_assigned_to_fk on relation bug"
Here is my user table:
\d users
                                         Table "users"
      Attribute       |         Type          |                    Modifier

----------------------+-----------------------+-----------------------------------
-------------
 user_id              | integer               | not null default nextval('users_pk
_seq'::text)
 user_name            | text                  | not null default ''
 email                | text                  | not null default ''
 user_pw              | character varying(32) | not null default ''
 realname             | character varying(32) | not null default ''
 status               | character(1)          | not null default 'A'
 shell                | character varying(20) | not null default '/bin/bash'
 unix_pw              | character varying(40) | not null default ''
 unix_status          | character(1)          | not null default 'N'
 unix_uid             | integer               | not null default '0'
 unix_box             | character varying(10) | not null default 'shell1'
 add_date             | integer               | not null default '0'
 confirm_hash         | character varying(32) |
 mail_siteupdates     | integer               | not null default '0'
 mail_va              | integer               | not null default '0'
 authorized_keys      | text                  |
 email_new            | text                  |
 people_view_skills   | integer               | not null default '0'
 people_resume        | text                  | not null default ''
 timezone             | character varying(64) | default 'GMT'
 language             | integer               | not null default '1'
 third_party          | integer               | not null default 1
 personal_status      | character(32)         |
 bemsid               | integer               |
 sensitive_info       | character(64)         |
 reason_access        | text                  |
 organization         | text                  |
 brass_first_time     | character(1)          | default '0'
 mail_sitenews_update | integer               | default '0'
 doclinks_sort_order  | character(1)          | default 'A'
Indices: idx_users_username,
         user_user,
         users_user_pw

Someone mentioned about using 'alter table'.  Would like would work?  But I'm not sure how to do it because \d doesn't
showthe constraint. 

Any ideas?
Mary

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, February 10, 2010 6:30 PM
To: Wang, Mary Y
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?

"Wang, Mary Y" <mary.y.wang@boeing.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
IMMEDIATEFOR 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