Hi I have the master-child tables as follows Master table create table foo(a numeric(10,0));
first child table foo1 as create table foo1 (check(a>=0 and a<10)) inherits(foo) second child table foo2 as create table foo1 (check(a>=10) inherits(foo) Now I create this function to redirect the inserts and updates on the master table. CREATE OR REPLACE FUNCTION foo_function () RETURNS trigger AS ' BEGIN IF (TG_OP = ''INSERT'') THEN IF NEW.a >= 0 and NEW.a <10 THEN INSERT INTO foo1 (a) values(new.a); ELSIF (NEW.a >=10 ) THEN INSERT INTO foo2 (a) values(new.a); end if; ELSIF (TG_OP = ''UPDATE'') THEN IF (OLD.a>=0 AND OLD.a < 10 AND NEW.a >= 10) THEN INSERT INTO foo2 (a) VALUES (NEW.a); DELETE FROM foo1 WHERE foo1.a = OLD.a; END IF; END IF; RETURN NULL; END ' LANGUAGE 'plpgsql' GO And I create a before insert/update trigger on foo CREATE TRIGGER FOO_TRIGGER BEFORE INSERT OR UPDATE ON FOO FOR EACH ROW EXECUTE PROCEDURE foo_function(); The insert statements are working properly.ie., they are inserting the rows into the corresponding child tables. But the update statements involving migration of row from 1 child to another is not working for example UPDATE FOO SET A=5 WHERE A=4 works fine becoz the updated row remains in the same child But UPDATE FOO SET A =20 WHERE A=4 gives the following error "new row for relation "foo1" violates check constraint "foo1_a_check" Why? All I wanted to do in this case is to insert that row into 'foo2' table and delete it from 'foo1' table . What am I doing wrong here? (Note: If I implement the same using rules it works fine!!!) Thanks in advance josh