Thread: Getting Results From Trigger

Getting Results From Trigger

From
cbell
Date:
Hello everyone, I was hoping someone could help me with this...

I'm running postgres 7.02 on redhat 6.2, apache 1.3.14 and mod_perl
1.24_01.  I'm also using perl modules DBI 1.14 and DBD-Pg-0.95 to acces
the Postgres database.

Everytime my inventory file gets updated, I would like to have the
quantity on hand returned to my perl script.  I set up the following
function to do this:

CREATE FUNCTION inv_lookup () RETURNS OPAQUE AS '
BEGIN
RAISE  NOTICE ''%'', NEW.qtyonhand;
RETURN new;
end;
'language 'plpgsql';

Then I create the trigger like this...

CREATE TRIGGER qty after inventory for each row execute procedure
inv_lookup();

If I do any sort of updates from within psql I get the correct data
which is the Qtyonhand field in this format:

NOTICE:  15

However, in my perl program, I'm unsure as to how to get this
information back.  The Postgres Documentation says that the results from

a Notice get sent back to the application, but I check the DBI->err,
DBI->errstr, and DBI->state and they are empty.  The result code for the

actual SQL command I run is just 1 for success.

Does anyone know how to get these results from within a perl scripts???
Thanks in advance for any help.

Chris.





Re: Getting Results From Trigger

From
Ian Harding
Date:
cbell wrote:

> Hello everyone, I was hoping someone could help me with this...
>
> I'm running postgres 7.02 on redhat 6.2, apache 1.3.14 and mod_perl
> 1.24_01.  I'm also using perl modules DBI 1.14 and DBD-Pg-0.95 to acces
> the Postgres database.
>
> Everytime my inventory file gets updated, I would like to have the
> quantity on hand returned to my perl script.  I set up the following
> function to do this:
>
> CREATE FUNCTION inv_lookup () RETURNS OPAQUE AS '
> BEGIN
> RAISE  NOTICE ''%'', NEW.qtyonhand;
> RETURN new;
> end;
> 'language 'plpgsql';
>
> Then I create the trigger like this...
>
> CREATE TRIGGER qty after inventory for each row execute procedure
> inv_lookup();
>
> If I do any sort of updates from within psql I get the correct data
> which is the Qtyonhand field in this format:
>
> NOTICE:  15
>
> However, in my perl program, I'm unsure as to how to get this
> information back.  The Postgres Documentation says that the results from
>
> a Notice get sent back to the application, but I check the DBI->err,
> DBI->errstr, and DBI->state and they are empty.  The result code for the
>
> actual SQL command I run is just 1 for success.
>
> Does anyone know how to get these results from within a perl scripts???
> Thanks in advance for any help.
>
> Chris.

I have spent a little time trying to get MS SQL Server triggers to give up
their secrets to my AOLServer scripts, but it seems it can't be done.  An
update, insert or delete query (called DML by AOLServer, whatever that
means) can't have any return value.

I found I was able to work around it, as I suspect you could, with another
query.  After the insert, just select the quantity on hand for the item in
question, right?

Ian