Re: Rolling back transactions

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 } } } } -- } } Uday Shankar } } Derivative Products Group } } Republic National Bank of New York } } uday@dpg.rnb.com } } (212) 525-8000 } } -- } Uday Shankar } Derivative Products Group } Republic National Bank of New York } uday@dpg.rnb.com } (212) 525-8000 -- Bill Ennis Voice: 312-474-7516 SSA Fax: 312-474-7460 500 W. Madison email: ennis@ssax.com ennis@accesschicago.net