problem with Insert triggers
jdeveza wrote:
<cut>
>Apparently, Informix insert triggers (unlike Sybase's and Oracle's) do
>not allow an update on any column of the triggering table (ie., an insert
>trigger cannot trigger an update of the same table).
Correct, at least for V5.xx I do not know about later versions
>The insert statement in the application code is likewise generic or when
>hard coded (using ANSI SQL) applies to all RDBMS. Most of the values for
>the columns in the inserts are not supplied but are rather computed or
>determined based on some logic in the insert triggers.
>This limitation on insert trigger will force me to modify the application
>code (on the client side) where necessary to treat insert statements as a
>special case to handle this problem with the Informix insert trigger.
>Modifying the C/C++ source code is not a problem (as it is just a matter
>of adding conditional compilation or pre-processing and adding voluminous
>code to pre-compute the values in the inserts and by the way, we are
>using Informix CLI (Call Level Interface and not ESQL-C where you have
>to go thru several steps like binding columns before you can execute a
>single SQL). The problem is the load being moved to the client side may
>seriously add to the existing network traffic.
You could consider using stored procedures to perform the inserts, it will
still mean
that you must modify your code but it will not increase the client side
processing.
What you can do is to execute procedures that take a record as a parameter.
So if you have a table:
create table fred (
x integer,
y char(10),
z date
)
Your procedure could be:
create procedure ins_fred(a integer, b char(10), c date);
-- Some dummy processing
let c = today;
insert into fred values (a, b, c);end procedure;
As a point of interest. You could [possibly] use this approach to do the
work for all of
the databases, although you might find yourself having to make some
procedures
specific to the database you are using eg oracle, sybase etc.
In the long run this approach might be more flexible. It only falls down if
you require the processing to be performed regardless of the way in which
the inserts are
performed, ie via your apps or through some other [eg sql scripts] process
that does
inserts.
Hope this helps
Mark Denham
BBC
London, UK
Mark.Denham@bbc.co.uk