Hi Mr. Wolfe,
Thanks for your response. I have received e-mails from several others. Thanks
for everyone and appreciate your help.
Mr. Wolfe sent a sample code. I took that as the base and debugged my code and
identified the issue. Now my code is working fine. I have identified the real
culprit.
Earlier my trigger was as like
CREATE OR REPLACE FUNCTION fun_update_payments() RETURNS TRIGGER AS
$trg_update_payments$
DECLARE
BEGIN
UPDATE jl
SET jl.outstanding = jl.outstanding - new.Principle_Amount
WHERE jl.jl_id=new.jl_id;
RETURN new;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE NOTICE 'fun_update_payment() Failed...';
END
$trg_update_payments$ LANGUAGE plpgsql;
After debugging I found my Update statement is wrong, I should not have prefix
as <table_name.> (Oracle accepts this.). I then changed that to as below and
stared working.
UPDATE jl
SET outstanding = outstanding - new.Principle_Amount
WHERE jl_id=new.jl_id;
Somehow Postgres is not capturing this at the compilation time.
But at run time, instead of throwing syntax error, it was trowing some
transactional error as "ERROR: cannot begin/end transactions in PL/pgSQL". The
reason for that is the EXCEPTION block that I had at the end.
I then removed below block from the trigger, then it was throwing expected
syntax error at run time.
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE NOTICE 'fun_update_payment() Failed...';
However it works now. Again thanks to Mr. Wolfe.
Thanks and Regards
Kaleeswaran Velu
________________________________
From: Wolfe Whalen <wo...@quios.net>
To: Kaleeswaran Velu <v_kal...@yahoo.com>
Cc: Postgres SQL List <pgsql-sql@postgresql.org>
Sent: Thursday, April 4, 2013 12:58 PM
Subject: Re: [SQL] Postgres trigger issue with update statement in it.
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
>
