Trigger in transaction

Hi

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

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