Re: Process in which to log certain Inserts, Updates, and Deletes

johnblessing@my-dejanews.com wrote: > > PMFJI > > I have a similar problem. I want to create a trigger which stores the > relevant delete statement in a separate table, every time a row is deleted in > several other tables. This is so I can replicate the deletion on a remote SQL > Anywhere database The body of the trigger will look something like: > > create trigger td_test delete on addr> referencing old as old_del > for each row > ( > INSERT INTO sm_delete( statement ) > VALUES ( 'DELETE FROM addr WHERE code =' || code ) ;> ) > > Where code is the primary key column of the addr table. I can't just store > the primary key value as some other tables have more than one primary key > column. Which also means I can't call a stored procedure as it would require > a variable number of parameters. For example, other tables might need: > > INSERT INTO sm_delete( statement ) > VALUES ( 'DELETE FROM test WHERE id =' || code || ' AND xx_id = ' || xx_id) ;> > The problem is that Informix throws up a syntax error when I include the > concatenation symbol ||. Even though || is a valid in a select. Surely > there is some way to do this? > > John > <snip> You cannot have an expression as an item in a VALUES list. There are two solutions you could try: INSERT INTO sm_delete( statement ) SELECT 'DELETE FROM test WHERE id =' || code || ' AND xx_id = ' ||xx_id FROM informix.systables WHERE tabid = 1; I haven't tested this to see if the parameter substitution works in triggers. OR call a stored procedure with one parameter indicating how many of the following parameters are actually used. Use IF-THEN-ELIF to select the correct code. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions. All opinions are my own and not those of Bayer plc. My Internet plumbing does not allow me to mail and post news together. Sorry. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/