Re: Rolling back transactions

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