I have a table with a reference constraint and an ON DELETE SET NULL action. When this action triggers, I also want to update another field in the table, actually a timestamp which should be set to NOW().
After reading some documentation it would seem a rule is the easiest way to accomplish this. However, I ran into a problem with this: CREATE TABLE a ( id INT PRIMARY KEY ); CREATE TABLE b ( id INT REFERENCES a ON DELETE SET NULL, time TIMESTAMP DEFAULT 'infinity' ); INSERT INTO a VALUES (1); INSERT INTO b VALUES (1); CREATE RULE b_id_null AS ON UPDATE TO b WHERE new.time='infinity' AND old.id IS NOT NULL AND new.id IS NULL DO UPDATE b SET time=NOW() where id=old.id; DELETE FROM a WHERE id=1; I would now expect a to by empty and b to contain a single row with id=NULL and time=NOW(). However, this is what I get: ERROR: query rewritten 10 times, may contain cycles ERROR: query rewritten 10 times, may contain cycles It would seem that my WHERE clause is not checked before the action is run. Is this simply not implemented (yet, hopefully)? Thanks. --- Kristian ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
