Received: from nts1.rtp.epa.gov (nts1.rtp.epa.gov [134.67.208.208]) by hub.org (8.10.1/8.10.1) with ESMTP id e79G4NG08299 for ; Wed, 9 Aug 2000 12:04:23 -0400 (EDT) Received: from epamail.epa.gov (IDENT:mvs@wolfpack.rtpnc.epa.gov [134.67.139.59]) by nts1.rtp.epa.gov (8.9.3/8.9.3) with ESMTP id MAA25186 for ; Wed, 9 Aug 2000 12:08:09 -0400 (EDT) Message-ID: <399180FD.2ACF8B2D@epamail.epa.gov> Date: Wed, 09 Aug 2000 12:04:13 -0400 From: Mark Volpe X-Mailer: Mozilla 4.74 [en] (X11; U; Linux 2.2.14-15mdk i586) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Rules aren't doing what I expect Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200008/70 Hi again, I have a table with a trigger that can potentially modify a row before it gets inserted or updated: CREATE TABLE t1 (a int); CREATE FUNCTION t1_validate() RETURNS opaque AS ' BEGIN IF (NEW.a>10) THEN NEW.a=10; END IF; IF (NEW.a<0) THEN NEW.a=0; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER t1_trig BEFORE INSERT OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE t1_validate(); I have another table that tracks changes in the first table with rules: CREATE TABLE t1_log (old_a int, new_a int); CREATE RULE t1_insert AS ON INSERT TO t1 DO INSERT INTO t1_log VALUES(NULL, NEW.a); CREATE RULE t1_update AS ON UPDATE TO t1 DO INSERT INTO t1_log VALUES(OLD.a, NEW.a); CREATE RULE t1_delete AS ON DELETE TO t1 DO INSERT INTO t1_log VALUES(OLD.a, NULL); When I try this out, however, the rule seems to use the original value, rather than the "corrected" value. INSERT INTO t1 VALUES(2); INSERT INTO t1 VALUES(8); INSERT INTO t1 VALUES(15); SELECT * FROM t1; a ---- 2 8 10 The table t1 shows the corrected value of 10, but, SELECT * FROM FROM t1_log; old_a | new_a -------+------- | 2 | 8 | 15 The t1_log table doesn't show what was actually inserted into t1! Are there any changes I can make to the logic above so that t1_log can show the correct value? Thanks, Mark