using commit / rollback in SQL script
Posted: Tue 15 May 2012 09:55
I'm creating a SQL regular to update another database with te same structure. Some queries are collected statements to put data in tables (several INSERTS). These data must only be committed when the collected queries are done. The script looks like this:
If table1 or table2 or table3 gets an error a rollback must be done and the end of the script must be reached. Is this possible in the script.
Code: Select all
set autocommit = 0
insert into table1 values ('table1_field1', 'table1_field2', 'table1_field3');
insert into table2 values ('table1_field1', 'table2_field2', 'table2_field3');
insert into table3 values ('table1_field1', 'table2_field2', 'table3_field3');
commit work;
insert into table4 values (1,2,3,4,5,6,7,8,9);
commit work;
set autocommit = 1