Re: Rolling back transactions

>Date: Wed, 20 Aug 1997 16:29:19 -0400 >From: Uday Shankar <uday@dpg.rnb.com> >X-Informix-List-Id: <list.16073> > >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. OK. You could also, in this case, use the INSERT directly in the trigger clause, but in general, executing an SP is often necessary. >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. That should be automatic. >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. That's correct; the error from the SP should cause the insert into u1 to fail. You can't rollback the transaction during the course of the statement, because the user is entitled to regain control and could get very upset if your trigger rolled back several hours worth of changes, especially if the program has a strategy to deal with insert failures into table u1. >Here is the schema I used: Thanks for including this - it helps no end. I used the material you provided to reproduce the problem, as discussed below... CREATE TABLE u1 ( f1 INTEGER, f2 INTEGER ); CREATE UNIQUE INDEX u1_ndx0 ON u1 (f1); 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; -- I moved the CREATE TRIGGER statement because you can't create the -- trigger before the procedure exists. CREATE TRIGGER tg_utrig INSERT ON u1 REFERENCING NEW AS p FOR EACH ROW (EXECUTE PROCEDURE sp_update_u2(p.f1 ,p.f2 )); >Suppose table u2 has a row (1,1). INSERT INTO u2(f1, f2) VALUES(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. Well, when I did: INSERT INTO u1 VALUES(1, 1); I got the error message: SQL -239: Could not insert new row - duplicate value in a UNIQUE INDEX column. ISAM -100: ISAM error: duplicate value for a record with unique key. and the table u1 was empty. I was working in a logged OnLine database, of course... When I reran the test you provided in an unlogged database, then I got the results you found (testing on Solaris 2.5.1 with OnLine 7.23.UC1). >How can I modify this SQL so the transaction is rolled back on the error? Does your database have transactions? If not, then you need convert the database to a logged database. If it does have transactions and still shows this behaviour, then I'd have to assume that it is an SE database, because the rules for SE databases are different (detached mode checking and all that). However, you say you have an OnLine 7.22 database; if your database is logged and you are getting the problem, then it is time to upgrade to 7.23.UC1 or later. I don't have my 7.22 OnLine system set up any more so I can't readily reproduce the problem on that... Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: Warning I do not reply to messages with anti-spam in the return path.