HI Guys,

   I am trying to create a trigger within EDB Manager that is fired after a
record is deleted in an invoice table..


   insert into invarch (invnumb, custnmbr) values(oldrow.invnumb,
oldrow.custnmbr);

   A couple of issues.

   1.   I get an error.."ElevateDB Error #700 An error was found in the statement
at line 4 and column 13 (Invalid expression invarch found, the referenced
cursor does not exist)"

   The table does exist, I can open it in EDB Mgr.  What have I missed?


   2.   There are 50+ fields in this table alone, it would be great do be able to
do something like this...

   insert into invarch (*) values(oldrow.*);

   Understanding that it is non-standard, it would nevertheless make it far more
convenient, and efficient whne creating such a trigger.


   Any thoughts/suggestions appreciated.

Regards,

Ian



--

Ian,

<< 1. I get an error.."ElevateDB Error #700 An error was found in the
statement at line 4 and column 13 (Invalid expression invarch found, the
referenced cursor does not exist)"

The table does exist, I can open it in EDB Mgr.  What have I missed? >>

You're using the dynamic cursor insertion syntax, and you need to use this
instead:

DECLARE InsertStmt STATEMENT;

PREPARE InsertStmt FROM 'insert into invarch (invnumb, custnmbr)
values(?,?)';
EXECUTE InsertStmt USING oldrow.invnumb, oldrow.custnmbr;

All of the valid SQL/PSM statements are detailed here:

http://www.elevatesoft.com/edb1sql_sql_psm_statements.htm

<< 2. There are 50+ fields in this table alone, it would be great do be able
to do something like this...

insert into invarch (*) values(oldrow.*);  >>

You will be able to do this once EDB has row variable support, which is on
the list of to-do items.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Tim Young [Elevate Software] wrote:

> Ian,
>
> << 1. I get an error.."ElevateDB Error #700 An error was found in the
> statement at line 4 and column 13 (Invalid expression invarch found, the
> referenced cursor does not exist)"
>
> The table does exist, I can open it in EDB Mgr.  What have I missed? >>
>
> You're using the dynamic cursor insertion syntax, and you need to use this
> instead:
>
> DECLARE InsertStmt STATEMENT;
>
> PREPARE InsertStmt FROM 'insert into invarch (invnumb, custnmbr) values(?,?)';
> EXECUTE InsertStmt USING oldrow.invnumb, oldrow.custnmbr;
>
> All of the valid SQL/PSM statements are detailed here:
>
> http://www.elevatesoft.com/edb1sql_sql_psm_statements.htm
>

OK.  I'm not familiar with the 'dynamic cursor' concept.  I presume the other
is 'static' or 'fixed' cursor?  Didn't even realise there was a difference.
I'm so far behind the times when it comes to SQL & SQL tables/concepts.

..
>
> insert into invarch (*) values(oldrow.*);  >>
>
> You will be able to do this once EDB has row variable support, which is on
> the list of to-do items.

Excellent.  Any rough time frame?

Regards & thanks,

Ian


--

Ian,

<< OK.  I'm not familiar with the 'dynamic cursor' concept.  I presume the
other is 'static' or 'fixed' cursor? >>

Forget that I said the word "dynamic".  I just confused the subject and it
doesn't mean anything in this context.

<< Excellent.  Any rough time frame? >>

Not as of now, no.

--
Tim Young
Elevate Software
www.elevatesoft.com