Re: Rolling back transactions

Ok, thanks. I was wondering if there was a way around this, since we typically do our testing in unlogged databases. I guess there really isn't a way to roll back unlogged transactions then, but I thought I'd ask anyway! Uday Bill Ennis wrote: } } If you don't have logging on the BEGIN WORK, COMMIT WORK, and } ROLLBACK WORK statements are not valid and will fail every time. } Logging is the mechanism used to recover during a rollback. } Without logging every database update is committed as soon as } it is executed. The only exeption is DDL (i.e. create table, create } index, etc.). So if you want to use transaction logic you'll need to } turn on logging first. } } -Bill } > } > One thing I forgot to mention. The database does not have } > logging turned on. } > } > I believe that if logging is turned on, the triggering statement } > and the triggered statements are rolled back, but that's not } > true for databases without logging. } > } > Thanks! } > } > Uday } > } > Uday Shankar wrote: } > } } > } Hi all, } > } } > } I have a situation here which I can't seem to find a satisfactory } > } solution with. I am using the Informix Online Dynamic server 7.22 } > } running on a Solaris box. } > } } > } I have two tables, u1 and u2. If I insert a row into table u1, I want } > } a trigger to fire and execute a stored procedure to insert } > } a value into table u2. } > } } > } If any error occurs, I'd like to rollback all the changes, including } > } the original insert statement., ie, if I can't insert a row into } > } table u2, I don't want the insert into table u1 to go through } > } either. } > } } > } I tried putting in a rollback statement in the procedure, but when } > } the trigger is invoked, I get error 744 which says you can't have } > } rollback statements in a trigger. } > } } > } Here is the schema I used: } > } } > } --- } > } } > } create table u1 } > } ( } > } f1 integer, } > } f2 integer } > } ); } > } } > } create unique index u1_ndx0 on u1 (f1); } > } } > } ---- } > } } > } create trigger tg_utrig insert on u1 } > } referencing new as p } > } for each row } > } (execute procedure sp_update_u2(p.f1 ,p.f2 )); } > } } > } ---- } > } } > } create table u2 } > } ( } > } f1 integer, } > } f2 integer, } > } who char(10) default user } > } ); } > } } > } create unique index u2_ndx0 on u2 (f1); } > } } > } --- } > } } > } create procedure sp_update_u2(i1 int, i2 int) } > } } > } define errno, isamerr int; } > } define errtext char(255); } > } } > } on exception } > } set errno, isamerr, errtext } > } raise exception errno, isamerr, errtext; } > } end exception } > } } > } insert into u2(f1,f2) values(i1, i2); } > } } > } end procedure; } > } } > } --- } > } } > } Suppose table u2 has a row (1,1). Now, if I attempt a insert } > } into u1 of the values (1,1), then I get a error message, } > } but a row is inserted into u1 anyway. } > } } > } How can I modify this SQL so the transaction is rolled back } > } on the error ? } > } } > } Thanks for your inputs! } > } } > } Uday