I am trying to apply Trasnactions (beginTransaction,rollback and commit. however if the query of adding invoice (Frst Query) executed successfully I want to update the supplier amount (Second Query). I intended to write wrong syntax in the second query by changing UPDATE to UPDATEEE. I assumed this should rollback. I got query syntax error message but the invoice added (first query successfully executed).
What is the wrong thing i am doing?
Invoice.addNewInvoice = function (invoice_data,result){
sql.beginTransaction(function(err){
if (err) { throw err; }
sql.query('INSERT INTO invoice SET ?',invoice_data, function(err,res){
if(err){
sql.rollback(function() {
throw err;
});
}else{
sql.query('UPDATEEEEE supplier SET supplier_amount = supplier_amount + ' + invoice_data.invoice_amount + ' WHERE supplier_id = ' + invoice_data.supplier_id, function(err,res){
if(err){
sql.rollback(function() {
throw err;
});
}
})
sql.commit(function(err) {
if (err) {
sql.rollback(function() {
throw err;
});
}else{
result(null,res);
}
});
}
});
});
}
The problem is that i set the commit to be executed even if the second query faild. it should be set on the else as below:
Invoice.addNewInvoice = function (invoice_data,result){
sql.beginTransaction(function(err){
if (err) { throw err; }
sql.query('INSERT INTO invoice SET ?',invoice_data, function(err,res){
if(err){
sql.rollback(function() {
throw err;
});
}else{
sql.query('UPDATEEEEE supplier SET supplier_amount = supplier_amount + ' + invoice_data.invoice_amount + ' WHERE supplier_id = ' + invoice_data.supplier_id, function(err,res){
if(err){
sql.rollback(function() {
throw err;
});
}else{
sql.commit(function(err) {
if (err) {
sql.rollback(function() {
throw err;
});
}else{
result(null,res);
}
});
}
})
}
});
});
}
This way it rollback since if the second query failed.