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

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 addrreferencing 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 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