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
                                              -- even though 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  |  [EMAIL PROTECTED]  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to