Re: Undo an update

2006. August 11. Friday 19.26 dátummal Judith ezt írta:
>
>     Is there a way to undo an update???
>

At the 1st time: I speak little english, so I sorry.
If I understand, what's your problem, maybe I can help you... Maybe...
So, if I'd like to save the inserted, deleted and/or updated rows, 
then I create an extra table and some rows, triggers...

-- Function: generate_log_table()
CREATE OR REPLACE FUNCTION generate_log_table() RETURNS "trigger" AS
$BODY$
DECLARE query text;
BEGINIF (TG_OP = 'INSERT') THEN    query := 'INSERT INTO data_table 
VALUES('||''''|| NEW.a ||''');'; ELSIF (TG_OP = 'UPDATE') THEN query := 'UPDATE data_table SET a = ' 
|| '''' || NEW.a || ''' WHERE id_table = '|| NEW.id_table ||';';ELSIF (TG_OP = 'DELETE') THEN query := 'DELETE FROM
data_tableWHERE 
 
id_table = '|| OLD.id_table ||';';END IF;INSERT INTO log_table (fecha, instruction) VALUES (now(), query); RETURN NEW;
END
$BODY$ LANGUAGE 'plpgsql' VOLATILE;

-- Table: data_table
CREATE TABLE data_table
( a text, id_table serial NOT NULL, CONSTRAINT table_pkey PRIMARY KEY (id_table)
) 
WITH OIDS;

-- Trigger: generate_log_table on data_table
CREATE TRIGGER generate_log_table AFTER INSERT OR UPDATE OR DELETE ON data_table FOR EACH ROW EXECUTE PROCEDURE
generate_log_table();

-- Table: log_table
CREATE TABLE log_table
( fecha timestamp, instruction text, id_table_log serial NOT NULL, CONSTRAINT log_table_pkey PRIMARY KEY
(id_table_log)
) 
WITH OIDS;


So, if you send every insert, update, delete command to a 
logger-table, then you can to undo anything.
I think...

Bye,
kjt


McAfee SCM 4.1 által ellenrizve!