Informix-SE: Aggregating a row into a string

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 contactsREFERENCING 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.

In article <80f3tj$7js$1@nnrp1.deja.com>, 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): I've tried this and can't get it to work, but you might have better luck. In a stored procedure, you can make a system call. Write a script that accepts the row id and unloads the record from the table. Then in the stored procedure put in a line that says SYSTEM 'script_name ' || rowid; It isn't a good practice, since if I remember correctly, anything called from a stored procedure runs as informix (seem to remember that, can't be sure), but it should work. Make sure the file permissions on the script only allow you (or root) to modify it, or you could get a hacker figuring out what was going on and changing the script. Also, make sure the full path to the script is called from the SP, so that someone can't make a trojan horse earlier in their path. -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.