[HACKERS] triggers and inheritance tree

[Jaime Casanova]

Hi,

i was trying to create triggers that redirect INSERT/UPDATE/DELETE
actions from parent to childs, but found that UPDATE/DELETE doesn't
get redirected. Actually, the triggers BEFORE UPDATE and BEFORE DELETE
aren't even fired.

I haven't tried with AFTER triggers to see if they are fired but i
tried on 8.4 to 9.1 and all of these have the same behaviour

attached is a simple contained test of this

PS: i'm hoping this is just me needed to sleep

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

drop database if exists test;
create database test;

\c test

create language plpgsql;

create schema schema1;
create schema schema2;

create table t1(i int primary key, t text);
create table schema1.t1() inherits (public.t1);
create table schema2.t1() inherits (public.t1);

create function f_insert_trigger() returns trigger as $$
begin
	raise notice 'trigger on %', TG_OP;

    insert into schema1.t1 values(new.i, new.t);
	return null;
end;
$$ language plpgsql;

create function f_update_trigger() returns trigger as $$
begin
	raise notice 'trigger on %', TG_OP;

    update schema1.t1 set i = new.i, t = new.t where i = old.i and t = old.t;
	return null;
end;
$$ language plpgsql;

create function f_delete_trigger() returns trigger as $$
begin
	raise notice 'trigger on %', TG_OP;

    delete from schema1.t1 where i = old.i and t = old.t;
	return null;
end;
$$ language plpgsql;

create trigger trg_insert before insert on public.t1 for each row execute procedure f_insert_trigger();
create trigger trg_update before update on public.t1 for each row execute procedure f_update_trigger();
create trigger trg_delete before delete on public.t1 for each row execute procedure f_delete_trigger();

-- insert some data
insert into schema1.t1 values(1, 'test');
insert into schema2.t1 values(2, 'test');
insert into schema1.t1 values(3, 'fixed row');

-- test triggers

-- ok, this one is redirected
insert into t1 values(4, 'redirected');
select * from t1;
select * from schema1.t1;

-- bad, update and delete don't respect the triggers

update t1 set t = t || ' updated' where t = 'test';
select * from t1;

delete from t1 where t = 'test updated';
select * from t1;


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers