Posting from the Informix-list

Thanks again to everyone for welcoming me to this list. I wasn't quite sure how to "bundle" my newbie questions, so instead I thought I'd mention an "application" I'd like to write (using Informix-SE) and point out the questions I have, as well as ask if anyone sees additional problems or has comments on how I could write the application better. I start with two tables: TABLE people: lastname char(255), firstname char(255), zipcode(255) TABLE comments: username char(255), timestamp datetime year to second, refid int, comment char(255) Basically, I want to be able to make a comment anytime I change (UPDATE or INSERT) 'people'. In a perfect world, I want to type something like: EXAMPLE 1>> insert into people values ('Frapples','Robert','12345') WITH COMMENT "met Bob at Halloween party" OR EXAMPLE 2>> update people set zipcode='12345' where zipcode='54321' WITH COMMENT "the 54321 zip code has now changed to the 12345 zip code" I realize I can't actually rewrite the SQL interpreter (nor would I want to), so in real life, this might be a procedure or something. The results I would want are: EXAMPLE 1>> insert into people values ('Frapples','Robert','12345') insert into comments values ("mathprof",current,***,"met Bob at Halloween party") where "***" would be the rowid of the row just inserted into 'people'. EXAMPLE 2>> update people set zipcode='12345' where zipcode='54321' FOR EACH ROW AFFECTED BY THE UPDATE: insert into comments values ("mathprof",current,***,"the 54321 zip code has now changed to the 12345 zip code") where "***" would be the rowid of each row affected by the update (so, if 15 rows were changed, this would insert 15 rows into comments) In this way, I would have a history of changes made to a given row (or at least a history of comments whenever that row was changed). Note: the username field is semi-useless since I'm the only one using my machine right now-- however, if we get into a situation where multiple people are updating a table, it's useful to know who did the actual update. For realistic purposes, I'm assuming I'll need to create a procedure that takes two arguments: the SQL command I want to execute, and the comment I want to enter. For examples, if I named the procedure XYZ: CALL PROCEDURE XYZ("insert into people values ('Frapples','Robert','12345')", "met Bob at Halloween party"); CALL PROCEDURE XYZ("update people set zipcode='12345' where zipcode='54321'", "the 54321 zip code has now changed to the 12345 zip code"); Here are the questions/problems/issues I had: *Is it possible to write a procedure that does what I want? A procedure that executes its first argument as an SQL command, stores what rows were changed/inserted, and then inserts rows into a second table based on those values? *In an absolutely ideal world, I'd like to enforce comments-- in other words, no one could UPDATE/INSERT the 'people' table without making a comment -- is there any way to do this? (eg, "table 'people' can only be updated by procedure xyz") *I believe that quoted strings in Informix-SE have a limit of 255 characters-- this isn't a problem for the comment (since I'm storing it in a char(255) anyway), but, in more complex situations, the SQL query could easily exceed 255 characters. How do I work around this? *In the 'comments' table, I'm using the 'rowid' from the 'people' table to refer to a specific record. For example, if the 'comments' table had a row like: username timestamp refid comment ======== ========= ===== ======================== mathprof 1999-01-01 00:00:00 7 Blah then it would refer to the record whose rowid is 7 in the 'people' table. Is this a good idea? Would 'update statistics' ruin my indexing? Is there a better way to refer to a specific row in another table? *Philosophically speaking, is my approach valid? Should I be using a completely different and unrelated approach? I've seen some discussion that a database should be used to STORE data, and that a secondary application program should be used to MANIPULATE data. I like the unified approach of having the database do everything, because the database knows a lot more about its own data than an external program. Thanks for any help!