Re: [NOVICE] DB insert Error

Harpreet Dhaliwal wrote:
> Forgot to write that that was my question.
> I mean can we call a stored procedure as an action of a trigger?

Sure, here's a working example from a running application:

CREATE OR REPLACE FUNCTION assign_detail_device_type_seq() RETURNS
trigger AS $$
BEGIN
IF NEW.det_device_type_id is NULL THEN
NEW.det_device_type_id := (SELECT
NEXTVAL('rumba.det_device_type_seq'));
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tDetDevType
BEFORE INSERT
ON rumba.DETAIL_DEVICE_TYPE
FOR EACH ROW
EXECUTE PROCEDURE assign_detail_device_type_seq();

>
>
> On 8/16/06, Harpreet Dhaliwal <harpreet(dot)dhaliwal01(at)gmail(dot)com> wrote:
> Its actually something like the trigger should start a C function
> after insert and the C function has the ECPG code for some more
> inserts.
> Its similar to the way we dynamically load a shared library while
> executing a stored procedure, as in , executing a fucntion in C file
> using stored procedure/ function.
>
> Harpreet
>
>
> On 8/16/06, Michael Fuhr <mike(at)fuhr(dot)org > wrote:
> On Wed, Aug 16, 2006 at 01:46:30AM -0400, Jasbinder Bali wrote:
>> I changed the datatype from varchar[] to varchar
>> ECPGdebug(1,stderr) says
>> [2998]: ECPGexecute line 97 Ok: INSERT 0 1
>>
>> Its not inserting any record even though sqlcode is 0.
>
> Are you committing the transaction? See the bottom of the
> following page:
>
> http://www.postgresql.org/docs/8.1/interactive/ecpg-commands.html
>
> "In the default mode, statements are committed only when EXEC SQL
> COMMIT is issued."

--
Guy Rouillier