Hi all,
I have a table "mytable" to log the validity of
data records with start and stop time.
To see, which records are still valid,
I created a view on all entries with stop=null.
The DB-Interaction should be done over the view,
so I added rules for INSERT, UPDATE an DELETE.
Insert and Update work fine, but the DELETE_RULE
stopps after the first UPDATE statement in the Rule-Body,
any further statements are ignored!!
Multiple Statements are not the Problem (Log=1,2),
and the first UPDATE statement works also. (Stop=now())
Is this a known Problem? Am I doing something wrong?
Is there any workaround for it?
Thanks, Peter
PS: Here is the code for testing, mylog should contain 1,2,3,4:
DROP VIEW myview;
DROP TABLE mytable;
DROP TABLE mylog;
CREATE TABLE mylog(id int);
CREATE TABLE mytable(id serial, proc text, start timestamp(4), stop
timestamp(4));
CREATE VIEW myview AS SELECT id, proc, start, stop FROM mytable WHERE stop IS
null;
CREATE RULE sri AS ON INSERT TO myview DO INSTEAD
INSERT INTO mytable (proc, start, stop) VALUES (new.proc, now(), null);
CREATE RULE srd AS ON DELETE TO myview DO INSTEAD
UPDATE mytable SET stop = now() WHERE id = old.id AND stop IS null;
CREATE RULE sru AS ON UPDATE TO myview DO INSTEAD (
INSERT INTO mylog (id) VALUES (1);
INSERT INTO mylog (id) VALUES (2);
UPDATE mytable SET stop = now() WHERE id = old.id AND stop IS null;
INSERT INTO mylog (id) VALUES (3);
UPDATE mytable SET stop = now() WHERE id = old.id+1 AND stop IS null;
INSERT INTO mylog (id) VALUES (4);
);
-- Insert some values works fine
INSERT INTO myview (proc) VALUES ('alpha');
INSERT INTO myview (proc) VALUES ('omega');
INSERT INTO myview (proc) VALUES ('gamma');
INSERT INTO myview (proc) VALUES ('delta');
-- Both Table and View are identical
SELECT * FROM mytable ORDER BY id;
SELECT * FROM myview ORDER BY id;
SELECT * FROM mylog ORDER BY id;
-- Delete a row works fine, too
DELETE FROM myview WHERE id = 4;
-- Row 4 is deleted
SELECT * FROM mytable ORDER BY id;
SELECT * FROM myview ORDER BY id;
SELECT * FROM mylog ORDER BY id;
-- !! The UPDATE_RULE does not work correct !!
UPDATE myview SET proc='beta' WHERE id = 2;
-- The Process 2 is updated, but there is no entry in the log
SELECT * FROM mytable ORDER BY id;
SELECT * FROM myview ORDER BY id;
SELECT * FROM mylog ORDER BY id;
--
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
