Can’t figure out why this transaction isn’t working. I’m not getting errors, and the notifications I’ve built into the if else aren’t even getting selected.
drop procedure if exists mergeups;
delimiter //
create procedure mergeups()
begin
declare sql_error int(1) default false;
declare continue handler for sqlexception
set sql_error = true;
#create a savepoint
savepoint beforemerge;
update invoices i
join vendors v using (vendor_id)
set i.vendor_id = (select vendor_id from vendors where vendor_name = 'federal express corporation')
where v.vendor_name = 'united parcel service';
delete from vendors
where vendor_name = 'united parcel service';
if sql_error = true then
rollback to beforemerge;
select 'transaction rolledback';
else
commit;
select 'transaction committed';
end if;
end//
delimiter ;