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
INSERTOR 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_deleteAS 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
