From: Kis János Tamás <kjt@takarnet.hu>
To: pgsql-sql@postgresql.org
Subject: Re: Undo an update
Date: Tue, 15 Aug 2006 14:20:06 +0200
Message-ID: <200608151420.07062.kjt@takarnet.hu> (raw)
In-Reply-To: <44DCBDD4.20507@correolux.com.mx>
References: <44DCBDD4.20507@correolux.com.mx>
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kjt@takarnet.hu
Subject: Re: Undo an update
In-Reply-To: <200608151420.07062.kjt@takarnet.hu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox