Informix trigger statement

[Patrick Chim]

Hi,

Does anyone know the informix trigger statement ? I have the following example

When inserting a table, if I encounter a field, say 'name', is a NULL value then the trigger statement should insert the table with the column a constant value "ABCDE" instead of NULL !

My HP-UX is 11.0 and Informix 7.31.UC6A !

Best Regards,
Patrick

[Rita C Workman]

No, this is not my forte....but I did find an interesting page that gave some examples from an Informix Users Group that may help you. Look past the intro...Here's the thread:

http://www.iiug.org/~waiug/old/iugnew44.htm

Regards,
Rita

[Deepak Extross · Accepted solution]

Hi,

I've only seen Triggers on insert used to cause an action on a different table; not update the same table as you want.

If you are doing the insert through an ESQL programme, you can add the logic for replacing nulls with your default value in the code before the insert.
Ditto for a shell script.

I'm not sure why you want to use a trigger, but if you can live without it, you can do what you are trying to do with a Stored Procedure.
Create a stored procedure say myval2insert(fieldname). This prodeure will return "ABCDE" if fieldname is NULL, or else fieldname itself.
Your insert statement would then be "INSERT into table myval2insert(fieldname)..."

Another option you may want to consider is to allow the database to hold NULLs, but while querying the table, do a
"SELECT nvl(field_name, \"ABCDE\") FROM table...".
This will return ABCDE if field_name is NULL.

For more info on triggers in Informix, see the SQL syntax guide for your Informix Engine at http://www.informix.com/answers/

HTH,
Deepak.