Trigger in transaction

Hi

I have a problem with triggers run inside transactions. I have a table called booking_user that should update a datefield on the linked table booking whenever a row is inserted. It works when transactions are in autocommit mode but not otherwise.

The following code does not work:

begin;

insert into booking (bookingid) values (118);

insert into booking_user (bookingid, userid) values (118,'root');
/* The trigger is run but booking.changedate is not updated */

commit;

The trigger and tables look like this:

Create table BOOKING (
BOOKINGID Numeric(9,0) Constraint SYS_C004702 NOT NULL ,
CHANGEDATE timestamp Default current_timestamp Constraint SYS_C004703 NOT NULL )
;

Create table BOOKING_USER (
BOOKINGID Numeric(9,0) Constraint SYS_C004737 NOT NULL ,
USERID Varchar(80) Constraint SYS_C004738 NOT NULL ,
;

create function trg_booking_user_i_f() returns trigger as '
begin
update booking set changedate=current_timestamp where bookingid=new.bookingid;
return new;
end;
' language plpgsql;
create trigger trg_booking_user_i before insert on booking_user for each row
execute procedure trg_booking_user_i_f();

This kind of code works in Oracle and in MS Sql.

I can't figure out why the changedate is not updated.
Are triggers run in same transaction as the update/insert or in some special?

Thank you

Daniel Carlsson
Gimlisoft AB
Email: daniel.carlsson@gimlisoft.se
Tel: 0709-744570, 031-189024