Hi Kaleeswaran,
We're glad to have you on the mailing list. I don't know enough about your trigger function to know exactly where it's going wrong, but I threw together a quick example that has an insert trigger on a child table that updates a row on the parent table. I'm hoping this might help. If it doesn't help, maybe you could give us a little more information about your function or tables. I'd be happy to help in any way that I can. CREATE TABLE survey_records ( name varchar(100), obsoleted timestamp DEFAULT NULL ); CREATE TABLE geo_surveys ( measurement integer ) INHERITS (survey_records); CREATE OR REPLACE FUNCTION obsolete_old_surveys() RETURNS trigger AS $$ BEGIN UPDATE survey_records SET obsoleted = clock_timestamp() WHERE survey_records.name = NEW.name AND survey_records.obsoleted IS NULL; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER obsolete_old_surveys_tr BEFORE INSERT ON geo_surveys FOR EACH ROW EXECUTE PROCEDURE obsolete_old_surveys(); INSERT INTO geo_surveys (name, measurement) VALUES ('Carbon Dioxide', 5); INSERT INTO geo_surveys (name, measurement) VALUES ('Carbon Dioxide', 10); INSERT INTO geo_surveys (name, measurement) VALUES ('Carbon Dioxide', 93); You'd wind up with something like this: SELECT * FROM survey_records; name | obsoleted ----------------+---------------------------- Carbon Dioxide | 2013-04-03 23:59:44.228225 Carbon Dioxide | 2013-04-03 23:59:53.66243 Carbon Dioxide | (3 rows) SELECT * FROM geo_surveys; name | obsoleted | measurement ----------------+----------------------------+------------- Carbon Dioxide | 2013-04-03 23:59:44.228225 | 5 Carbon Dioxide | 2013-04-03 23:59:53.66243 | 10 Carbon Dioxide | | 93 (3 rows) The parent survey_records is actually updating the child table rows when you do an update. Parent tables can almost seem like a view in that respect. You would have to be a bit careful if you're going to have an update trigger on a child that updated the parent table. It's easy to wind up with a loop like this: Child: Update row 1 -> Trigger function -> Update Row 1 on parent ->Parent: Let's see... Row 1 is contained in this child table, so let's update it there. ->Child: Update row 1 -> Trigger function -> Update Row 1 on parent ->Parent: Let's see... Row 1 is contained in this child table, so let's update it there. ... etc etc. Best Regards, Wolfe -- Wolfe Whalen wo...@quios.net On Wed, Apr 3, 2013, at 09:08 PM, Kaleeswaran Velu wrote: Hello Friends, I am new to Postgres DB. Recently installed Postgres 9.2. Facing an issue with very simple trigger, tried to resolve myself by reading documents or google search but no luck. I have a table A(parent) and table B (child). There is a BEFORE INSERT OR UPDATE trigger attached in table B. This trigger has a update statement in it. This update statement should update a respective record in table A when ever there is any insert/update happen in table B. The issue here is where ever I insert/update record in table B, getting an error as below : ********** Error ********** ERROR: cannot begin/end transactions in PL/pgSQL SQL state: 0A000 Hint: Use a BEGIN block with an EXCEPTION clause instead. Context: PL/pgSQL function func_update_payment() line 53 at SQL statement Line no 53 in the above error message is an update statement. If I comment out the update statement, trigger works fine. Can anyone shed some lights on this? Your help is appreciated. Thanks and Regards Kaleeswaran Velu