Q:

How to constrain insertion into database

What is the best way to add a constraint to the insertion action to a database? I have a small-ish table where one of the columns is a version number. I would like to verify that the version number only ever increases with each row inserted. I am currently doing this by getting the latest version number from the table and doing the conditional check on the 'code-side' (rather than 'db-side'). I am wondering if there is a more elegant way to do this? (e.g. adding constraints that won't invalidate the rest of the table). I'm using informix by the way.

A:

You can use trigger. There is example for INSERT statement:
create trigger check_ver_trg insert on my_small_table
referencing new as new
for each row (execute procedure check_version(new.version));

In check_version() you can use RAISE EXCEPTION as it is described at: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls949.htm

