Thread: view triggers/procedures
Hi, I have a table where in lot of triggers were included in that as shown below. How to view/access triggers and procedures from postgresql. I am using postgresql 7.4.1 on solaris. regds -raju Process=# \d reviews Table "public.reviews" Column | Type | Modifiers ------------------+-----------------------+--------------------------------- -------------------------- review_id | integer | not null default nextval('"reviews_review_id_seq"'::text) project | character varying(32) | not null phase | character varying(32) | not null default 'Unknown'::character varying artifact_type | character varying(32) | not null description | text | author | character varying(32) | moderator | character varying(32) | meeting_end_date | date | meeting_date | date | formal | boolean | default true status | character varying(16) | disposition | character varying(16) | default 'To be decided'::character varying comments | text | num_attendees | smallint | review_size | smallint | kickoff_time | smallint | meeting_time | smallint | review_name | character varying(64) | rework_effort | integer | default 0 Indexes: "reviews_pkey" primary key, btree (review_id) Triggers: "RI_ConstraintTrigger_414655" AFTER DELETE ON reviews FROM documents NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del"('<unnamed>', 'documents', 'reviews', 'UNSPECIFIED', 'review_id', 'review_id') "RI_ConstraintTrigger_414656" AFTER UPDATE ON reviews FROM documents NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd"('<unnamed>', 'documents', 'reviews', 'UNSPECIFIED', 'review_id', 'review_id') "RI_ConstraintTrigger_414658" AFTER DELETE ON reviews FROM inspectors NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del"('<unnamed>', 'inspectors', 'reviews', 'UNSPECIFIED', 'review_id', 'review_id') "RI_ConstraintTrigger_414659" AFTER UPDATE ON reviews FROM inspectors NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd"('<unnamed>', 'inspectors', 'reviews', 'UNSPECIFIED', 'review_id', 'review_id') "RI_ConstraintTrigger_414661" AFTER DELETE ON reviews FROM defects NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del"('<unnamed>', 'defects', 'reviews', 'UNSPECIFIED', 'review_id', 'review_id') "RI_ConstraintTrigger_414662" AFTER UPDATE ON reviews FROM defects NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd"('<unnamed>', 'defects', 'reviews', 'UNSPECIFIED', 'review_id', 'review_id')
Raju, these triggers are the PG implementation of foreign key constraints checking. They are written in C, so if you're curios how they work you will have to check the postgres sources ;-) HTH, Csaba. On Mon, 2004-08-23 at 12:06, SVGK, Raju (Raju) wrote: > Hi, > > I have a table where in lot of triggers were included in that as shown > below. How to view/access triggers and procedures from postgresql. > > I am using postgresql 7.4.1 on solaris. > > regds > -raju > > > Process=# \d reviews > Table "public.reviews" > Column | Type | > Modifiers > ------------------+-----------------------+--------------------------------- > -------------------------- > review_id | integer | not null default > nextval('"reviews_review_id_seq"'::text) > project | character varying(32) | not null > phase | character varying(32) | not null default > 'Unknown'::character varying > artifact_type | character varying(32) | not null > description | text | > author | character varying(32) | > moderator | character varying(32) | > meeting_end_date | date | > meeting_date | date | > formal | boolean | default true > status | character varying(16) | > disposition | character varying(16) | default 'To be > decided'::character varying > comments | text | > num_attendees | smallint | > review_size | smallint | > kickoff_time | smallint | > meeting_time | smallint | > review_name | character varying(64) | > rework_effort | integer | default 0 > Indexes: > "reviews_pkey" primary key, btree (review_id) > Triggers: > "RI_ConstraintTrigger_414655" AFTER DELETE ON reviews FROM documents NOT > DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > "RI_FKey_noaction_del"('<unnamed>', 'documents', 'reviews', 'UNSPECIFIED', > 'review_id', 'review_id') > "RI_ConstraintTrigger_414656" AFTER UPDATE ON reviews FROM documents NOT > DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > "RI_FKey_noaction_upd"('<unnamed>', 'documents', 'reviews', 'UNSPECIFIED', > 'review_id', 'review_id') > "RI_ConstraintTrigger_414658" AFTER DELETE ON reviews FROM inspectors > NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > "RI_FKey_noaction_del"('<unnamed>', 'inspectors', 'reviews', 'UNSPECIFIED', > 'review_id', 'review_id') > "RI_ConstraintTrigger_414659" AFTER UPDATE ON reviews FROM inspectors > NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > "RI_FKey_noaction_upd"('<unnamed>', 'inspectors', 'reviews', 'UNSPECIFIED', > 'review_id', 'review_id') > "RI_ConstraintTrigger_414661" AFTER DELETE ON reviews FROM defects NOT > DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > "RI_FKey_noaction_del"('<unnamed>', 'defects', 'reviews', 'UNSPECIFIED', > 'review_id', 'review_id') > "RI_ConstraintTrigger_414662" AFTER UPDATE ON reviews FROM defects NOT > DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > "RI_FKey_noaction_upd"('<unnamed>', 'defects', 'reviews', 'UNSPECIFIED', > 'review_id', 'review_id') > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
Csaba, Thank you for your reply. How do I check the sources. Can you please tell me in detail. regds -raju -----Original Message----- From: Csaba Nagy [mailto:nagy@ecircle-ag.com] Sent: Monday, August 23, 2004 4:11 PM To: SVGK, Raju (Raju) Cc: 'pgsql-general@postgresql.org' Subject: Re: [GENERAL] view triggers/procedures Raju, these triggers are the PG implementation of foreign key constraints checking. They are written in C, so if you're curios how they work you will have to check the postgres sources ;-) HTH, Csaba. On Mon, 2004-08-23 at 12:06, SVGK, Raju (Raju) wrote: > Hi, > > I have a table where in lot of triggers were included in that as shown > below. How to view/access triggers and procedures from postgresql. > > I am using postgresql 7.4.1 on solaris. > > regds > -raju > > > Process=# \d reviews > Table "public.reviews" > Column | Type | > Modifiers > ------------------+-----------------------+--------------------------------- > -------------------------- > review_id | integer | not null default > nextval('"reviews_review_id_seq"'::text) > project | character varying(32) | not null > phase | character varying(32) | not null default > 'Unknown'::character varying > artifact_type | character varying(32) | not null > description | text | > author | character varying(32) | > moderator | character varying(32) | > meeting_end_date | date | > meeting_date | date | > formal | boolean | default true > status | character varying(16) | > disposition | character varying(16) | default 'To be > decided'::character varying > comments | text | > num_attendees | smallint | > review_size | smallint | > kickoff_time | smallint | > meeting_time | smallint | > review_name | character varying(64) | > rework_effort | integer | default 0 > Indexes: > "reviews_pkey" primary key, btree (review_id) > Triggers: > "RI_ConstraintTrigger_414655" AFTER DELETE ON reviews FROM documents NOT > DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > "RI_FKey_noaction_del"('<unnamed>', 'documents', 'reviews', 'UNSPECIFIED', > 'review_id', 'review_id') > "RI_ConstraintTrigger_414656" AFTER UPDATE ON reviews FROM documents NOT > DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > "RI_FKey_noaction_upd"('<unnamed>', 'documents', 'reviews', 'UNSPECIFIED', > 'review_id', 'review_id') > "RI_ConstraintTrigger_414658" AFTER DELETE ON reviews FROM inspectors > NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > "RI_FKey_noaction_del"('<unnamed>', 'inspectors', 'reviews', 'UNSPECIFIED', > 'review_id', 'review_id') > "RI_ConstraintTrigger_414659" AFTER UPDATE ON reviews FROM inspectors > NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > "RI_FKey_noaction_upd"('<unnamed>', 'inspectors', 'reviews', 'UNSPECIFIED', > 'review_id', 'review_id') > "RI_ConstraintTrigger_414661" AFTER DELETE ON reviews FROM defects NOT > DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > "RI_FKey_noaction_del"('<unnamed>', 'defects', 'reviews', 'UNSPECIFIED', > 'review_id', 'review_id') > "RI_ConstraintTrigger_414662" AFTER UPDATE ON reviews FROM defects NOT > DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > "RI_FKey_noaction_upd"('<unnamed>', 'defects', 'reviews', 'UNSPECIFIED', > 'review_id', 'review_id') > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
Raju, The file with the source code is: /pgsql_sources_directory(replace_this)/src/backend/utils/adt/ri_triggers.c But you can trust that those triggers are not doing anything else then checking/enforcing the foreign keys you have defined on that table/referencing that table. So you might want instead to check what foreign keys are defined... You can figure them out from the parameters of the trigger functions, e.g.: RI_FKey_noaction_del"('<unnamed>', 'documents', 'reviews', 'UNSPECIFIED', 'review_id', 'review_id') should mean you have a foreign key pointing from the documents table review_id field to the reviews table review_id field. I'm not sure if the parent/child is not the other way around, but you will surely figure out from your schema. HTH, Csaba. On Mon, 2004-08-23 at 12:44, SVGK, Raju (Raju) wrote: > Csaba, > Thank you for your reply. How do I check the sources. Can you please tell me > in detail. > regds > -raju > > > -----Original Message----- > From: Csaba Nagy [mailto:nagy@ecircle-ag.com] > Sent: Monday, August 23, 2004 4:11 PM > To: SVGK, Raju (Raju) > Cc: 'pgsql-general@postgresql.org' > Subject: Re: [GENERAL] view triggers/procedures > > > > Raju, > > these triggers are the PG implementation of foreign key constraints > checking. > They are written in C, so if you're curios how they work you will have > to check the postgres sources ;-) > > HTH, > Csaba. > > > On Mon, 2004-08-23 at 12:06, SVGK, Raju (Raju) wrote: > > Hi, > > > > I have a table where in lot of triggers were included in that as shown > > below. How to view/access triggers and procedures from postgresql. > > > > I am using postgresql 7.4.1 on solaris. > > > > regds > > -raju > > > > > > Process=# \d reviews > > Table "public.reviews" > > Column | Type | > > Modifiers > > > ------------------+-----------------------+--------------------------------- > > -------------------------- > > review_id | integer | not null default > > nextval('"reviews_review_id_seq"'::text) > > project | character varying(32) | not null > > phase | character varying(32) | not null default > > 'Unknown'::character varying > > artifact_type | character varying(32) | not null > > description | text | > > author | character varying(32) | > > moderator | character varying(32) | > > meeting_end_date | date | > > meeting_date | date | > > formal | boolean | default true > > status | character varying(16) | > > disposition | character varying(16) | default 'To be > > decided'::character varying > > comments | text | > > num_attendees | smallint | > > review_size | smallint | > > kickoff_time | smallint | > > meeting_time | smallint | > > review_name | character varying(64) | > > rework_effort | integer | default 0 > > Indexes: > > "reviews_pkey" primary key, btree (review_id) > > Triggers: > > "RI_ConstraintTrigger_414655" AFTER DELETE ON reviews FROM documents > NOT > > DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > > "RI_FKey_noaction_del"('<unnamed>', 'documents', 'reviews', 'UNSPECIFIED', > > 'review_id', 'review_id') > > "RI_ConstraintTrigger_414656" AFTER UPDATE ON reviews FROM documents > NOT > > DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > > "RI_FKey_noaction_upd"('<unnamed>', 'documents', 'reviews', 'UNSPECIFIED', > > 'review_id', 'review_id') > > "RI_ConstraintTrigger_414658" AFTER DELETE ON reviews FROM inspectors > > NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > > "RI_FKey_noaction_del"('<unnamed>', 'inspectors', 'reviews', > 'UNSPECIFIED', > > 'review_id', 'review_id') > > "RI_ConstraintTrigger_414659" AFTER UPDATE ON reviews FROM inspectors > > NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > > "RI_FKey_noaction_upd"('<unnamed>', 'inspectors', 'reviews', > 'UNSPECIFIED', > > 'review_id', 'review_id') > > "RI_ConstraintTrigger_414661" AFTER DELETE ON reviews FROM defects NOT > > DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > > "RI_FKey_noaction_del"('<unnamed>', 'defects', 'reviews', 'UNSPECIFIED', > > 'review_id', 'review_id') > > "RI_ConstraintTrigger_414662" AFTER UPDATE ON reviews FROM defects NOT > > DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE > > "RI_FKey_noaction_upd"('<unnamed>', 'defects', 'reviews', 'UNSPECIFIED', > > 'review_id', 'review_id') > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html