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