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