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;
BEGIN
IF (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_table WHERE
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!