Rolling back transactions
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 u1referencing 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