On Sat, 20 Oct 2001, Aasmund Midttun Godal wrote:
> Can a rule see the where statement in a query which it has been
> triggered by? or is it simply ignored?? what happens?
>
Looking over your question, I wanted to clarify the problem a bit, so:
(cleaned up example a bit from Aasmund)
-- set up tables
drop view normal;
drop view dbl;
drop table raw;
CREATE TABLE raw (id INT PRIMARY KEY, name TEXT );
INSERT INTO raw VALUES(1, 'a');
INSERT INTO raw VALUES(2, 'b');
INSERT INTO raw VALUES(12, 'c');
INSERT INTO raw VALUES(15, 'd');
INSERT INTO raw VALUES(14, 'e');
-- set up two views: "normal", a simple view,
-- and "dbl", which shows id * 2
-- create basic rules to allow update to both views
CREATE VIEW normal AS SELECT * FROM raw;
CREATE RULE normal_update AS ON UPDATE TO normal DO INSTEAD UPDATE raw SET
id = NEW.id, name = NEW.name WHERE OLD.id = id;
CREATE VIEW dbl AS SELECT id * 2 as id, name FROM raw;
CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET
id = NEW.id, name = NEW.name WHERE OLD.id = id;
-- now test this
UPDATE normal SET id = id + 10 where id > 10; -- works fine
UPDATE dbl SET id = id + 10 where id > 10; -- above shows UPDATE 0 --
eventhough there are ids > 10
UPDATE dbl SET id = id + 10; -- UPDATE 1; shows table
SELECT * FROM dbl; -- inconsistencies: two "a"s
SELECT * FROM raw;
The issue is that there are no IDs over 10 that have another ID that is
exactly their value, so the first update to "dbl" does nothing.
The second time, w/o the ID>10 restriction, it finds 1(a), and double
that, 2(b), and adds 10; getting confused about which record to edit.
Is this the best way to interpret this? Is this a bug?
--
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
