RE: Informix-SE: Aggregating a row into a string

If you have isql or dbschema, you can build SQL files from the SP : LET l_sql1 = "echo ' unload to /tmp/contact.before select * from contact where oid = ",x," ' >/tmp/sql1.sql" LET l_sql2 = "echo ' unload to /tmp/contact.after select * from contact where oid = ",y," ' >/tmp/sql2.sql" and run isql/dbaccess against it using SYSTEM ' isql -s sql1.sql ' and SYSTEM ' isql -s sql2.sql ' in your SP and mail you the /tmp/contact.before and contact.after later I haven't tested it but should work. Good luck! Peter On Friday, November 12, 1999 8:03 AM, sgupta@atip.org [SMTP:sgupta@atip.org] wrote: > I'm using Informix-SE, and trying to set it up so that whenever anyone > changes a row in a certain table, I get an email detailing the > change. Here's what I have so far (oid is a serial field in the table > contacts): > > DROP TRIGGER mod_contacts; > CREATE TRIGGER mod_contacts UPDATE on contacts> REFERENCING old as x new as y FOR EACH ROW > (EXECUTE PROCEDURE scream(x.oid,y.oid)); > > DROP PROCEDURE scream; > create procedure scream (x int, y int)> SYSTEM '/usr/bin/echo OLDOID:'||x||' NEWOID: '||y||'| /usr/ucb/Mail -s > SCREAM root'; > END PROCEDURE; > > This sort of does what I want-- whenever anyone updates the table > contacts, I get a list of the oids of the rows altered. Since oid is a > serial field, I know specifically which rows were changed. > > However, I'd like to get more information-- specifically, I'd like to > see the entire altered row(s) before and after the alteration. Is > there any easy way to do this? Here are the things I've considered: > > % Have the trigger do something like: > > ... BEFORE UPDATE (EXECUTE PROCEDURE scream1(x)) > ... AFTER UPDATE (EXECUTE PROCEDURE scream2(x)) > > where scream1() would have something like: > > unload to /tmp/tempfile.before select * from contacts where oid=x.oid;> > and scream2() would have something like: > > unload to /tmp/tempfile.after select * from contacts where oid=x.oid;> > and then mail the /tmp/tempfile.* files to myself. Sadly, "unload" is > disallowed in procedures, so this won't work. > > % Send each field of the contacts table to the scream() procedure by > name. Something like: > > EXECUTE PROCEDURE scream(x.firstname, x.lastname, x.address, x.phone, > x.fax, y.firstname, y.lastname, y.address, y.phone, y.fax)> > This is really ugly, and completely table-dependant (it also has to be > updated if the table is ever ALTERed). I'd like to find a solution > that I can quickly port to other tables. > > % Use an aggregate function that converts/catenates the output of a > select statement into a simple string, which I could then pass on to > scream(): > > ... BEFORE UPDATE EXECUTE PROCEDURE scream1(x); > > where scream1(x) includes: > > LET a = select magicfunction(*) from contacts where oid=x; > > and similarly for AFTER UPDATE, where magicfunction changes the row: > > Bob,Smith,123 Maple St,1-212-555-1212 > > into the single string > > Bob|Smith|123 Maple St|1-212-555-1212| > > ie, an aggregate function equivalent of unload. Sadly, I could find no > such magic fucntion. > > % Use a column-based looping operator to build up a string to email > myself: > > ... BEFORE UPDATE EXECUTE PROCEDURE scream1(x); > > where scream1(x) includes: > > FOREACH y in (SELECT * from contacts WHERE oid=x) > result=result||y; > END FOREACH; > > I could then mail myself the variable "result", which is a catenation > of all the columns. The above example doesn't actually work, of > course, because FOREACH loops through rows, not through columns. Is > there a columnar equivalent? > > Any other thoughts on what I could do? > > Sincerely, Sarang Gupta (sgupta@atip.org), ATIP/ETIP/NMJC MIS Manager > > > Sent via Deja.com http://www.deja.com/ > Before you buy.