From: | Daniel Carlsson <daniel(dot)carlsson(at)gimlisoft(dot)se> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Trigger in transaction |
Date: | 2003-05-24 10:08:48 |
Message-ID: | buffalo1_3ecf44028deaf@webmail.buffalosoftware.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)carlsson(at)gimlisoft(dot)se
Tel: 0709-744570, 031-189024
From | Date | Subject | |
---|---|---|---|
Next Message | Martin Marques | 2003-05-24 13:50:17 | Re: Postgresql on SUN Server |
Previous Message | Yudha Setiawan | 2003-05-24 07:44:36 | Re-Create Table make Faster. |