Subject Re: [IBO] Multiple SQL Statements in Edit-/Insert-/DeleteSQL
Author Helen Borrie
At 03:23 PM 12/07/2004 +0200, you wrote:
> is it possible to have multiple SQL statements in the Edit-, Insert- or
>Delete SQL fields in a TIB_Query?
>I'd like to do something like this in an InsertSQL:
>insert into foo( a, b ) values ( :a, :b );
>insert into bar( c ) values ( :c );
>because the select looks like
>select a, b, c from foo left join bar on

No. A DML statement can operate on one and only one table.

>and I don't want to add a stored procedure for this more
>or less trivial task.

Updating two tables is not a trivial task. If you have to update both
tables, you have no option other than a stored procedure (which, itself,
can be quite trivial).

>I can't use the BeforeInsert event, because bar depends
>on foo (foreign key set).
>And I can't use AfterInsert because I'd have to Refresh the
>data after I've done the insert.

You simply can't do it, period. It's against database rules.

>OnCustomInsert would work,

Not. OnCustomInsert refers to a custom InsertSQL operation: inserting a
row of data into a single table using your own parameter assignments; or
calling a parameterised stored procedure.

>but the InsertSQL is less to type and maintain, IMHO.

It certainly is; and, if you are careful with the names you give to the
input parameters, you won't have to code anything.