Hi Tom,
thanks for your answer, I have also thought of combining
the statements, but my SQL-knowledge is too small for that.
I thought, the example with "mylog" would be better to
demonstrate the problem, but it's missing the point.
Below, if have added the code with my real problem.
What I want to do is a log of all starts and stops of validity.
So if a record is altered, I want the current one to be marked
by setting the stop-field to now() and another row to be added
to "mytable" with the same ID like the previous and start=now().
The Code below does only the mark-deleted-thing,
but does not insert a new record.
There is no unique-constraint on ID, I can add lines manually.
I also tried to swap the two lines in my Rule,
then a new row is inserted (good), but it is set
to end!=null by the second statement. (bad)
Maybe someone can give me a hint,
what's wrong with my code or my thinking?
Thanks, Peter
PS: Here's the NEW code with the uncooperative update-rule:
DROP VIEW myview;
DROP TABLE mytable;
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
(
UPDATE mytable SET stop = now() WHERE id = old.id AND stop IS null;
INSERT INTO myview (id, proc, start, stop) VALUES (old.id, old.proc, now(), null);
);
-- Insert some values works fine
INSERT INTO myview (proc) VALUES ('alpha');
INSERT INTO myview (proc) VALUES ('omega');
INSERT INTO myview (proc) VALUES ('gamma');
-- Both Table and View are identical
SELECT * FROM mytable ORDER BY id;
SELECT * FROM myview 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;
--
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kanns mit allen: http://www.gmx.net/de/go/multimessenger
