On Sat, 20 Oct 2001, Joel Burton wrote:
> 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) > 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; > 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? Don't think so. I think the rule doesn't make any sense. NEW.id and OLD.id are probably dbl values, so saying OLD.id=id (where id is raw.id since that's the update table) isn't correct. It probably should be OLD.id=id*2 (which seems to work for me, btw) It's editing a different row than the one that's being selected. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
