Re: CREATE RULE ON UPDATE/DELETE

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 | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant