Hi, I'm trying to write a trigger function, that would update an 'associated' TEBLE on INSERT to master table: CREATE TABLE master (id int not null unique, info text, ....); CREATE TABLE aux (master int references master(id), info text, ...); CREATE FUNCTION adjust() RETURNS "trigger" AS $$ BEGIN new.id := 1000-old.id; INSERT INTO aux (master, info) VALUES (new.id, 'hello world'); RETURN new; END $$ LANGUAGE plpgsql; CREATE TRIGGER pico BEFORE INSERT ON master FOR EACH ROW EXECUTE PROCEDURE adjust(); Note, that the trigger function ADJUST() *computs* the ID value for MASTER. The value computed complies to all MASTER table constraints, so it should be usable as foreing key for the insert statement that follows, but it isn't - I get foreign key reference violation fault at that point. Now, normaly (e.g. in SQL sequence as typed into the psql command line utility), when I: BEGIN; INSERT INTO master (id,...)...; SELECT * from master; ROLLBACK; SELECT * from master; ... the first SELECT above shows the newly inserted values, despite the fact, that the transaction didn't commit, yet. And when the transaction rolls back, the data automagically disapear (second SELECT). I understand this is normal. But in my trigger function "adjust()", executed within a transaction opened by "INSERT INTO master", the 'fresh' data (set by "new.id := value") isn't visible to other commands (like: INSERT INTO aux...). Is this intentional? Theoretically: would it violate corrent database application coding style/standards (SQL standard?), if the new.* data was in fact visible for statements inside such transaction, like the INSERT AUX above? BTW: May be there is other solution for my "INSERT ... AUX" which I cannot see myself? One thing, though: I cannot have TRIGGER AFTER INSERT do the job of putting the correct data into AUX TABLE, since that table takes intermediate data used during MASTER.ID computation, and it is required, that those intermediate data never-ever get into the MASTER table itself (or any other table apart from the AUX table - where they must reside. It's one of the reasons for AUX table to exist). -- -R
