Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Informix trigger statement

SOLVED
Go to solution
Patrick Chim
Trusted Contributor

Informix trigger statement

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
2 REPLIES
Rita C Workman
Honored Contributor

Re: Informix trigger statement

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
Honored Contributor
Solution

Re: Informix trigger statement

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.