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

In article <6rbevp$elf$1@nnrp1.dejanews.com>, johnblessing@my- dejanews.com writes >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 So add a serial column to them and pass that... a well written app would at most require a recompile.. >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 > >In article <6ql5fi$1rc1@webint.na.informix.com>, > probertsXXXXX@informix.com wrote: >> >> I'm not sure if this is what you have in mind, but here is how I track >> changes to my table "TABLE1", using triggers to add records to the history >> table "hist_TABLE1". It seems to be working well. >> >> create table "isdba".TABLE1 >> ( >> col_A char(5) not null, >> col_B char(30) not null >> ); >> >> create trigger "isdba".ins_TABLE1 insert on "isdba".TABLE1 >> referencing new as post >> for each row >> ( >> insert into "isdba".hist_TABLE1 (action, col_A, col_B, usr_name, >act_dt) >> values ('I', post.col_A, post.col_B, USER, CURRENT year to >second) >> ); >> >> create trigger "isdba".del_TABLE1 delete on "isdba".TABLE1 >> referencing old as pre >> for each row >> ( >> insert into "isdba".hist_TABLE1 (action, col_A, col_B, usr_name, >act_dt) >> values ('D', pre.col_A, pre.col_B, USER, CURRENT year to >second) >> ); >> >> create trigger "isdba".upd_TABLE1 update on "isdba".TABLE1 >> referencing new as post >> for each row >> ( >> insert into "isdba".hist_TABLE1 (action, col_A, col_B, usr_name, >act_dt) >> values ('U', post.col_A, post.col_B, USER, CURRENT year to >second) >> ); >> >> create table "isdba".hist_TABLE1 >> ( >> action char(1), >> col_A char(5), >> col_B char(30), >> usr_name char(8), >> act_dt datetime year to second >> ); >> >> - Paul (not a spokesman) >> > > >-----== Posted via Deja News, The Leader in Internet Discussion ==----- >http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care