-- Exercise of Chapter 14 in Murach' MySQL
-- Data created by Phạm Phúc Đức Anh_FIT.HNUE
-- Question 1
use ap;
drop procedure if exists test;
delimiter //
create procedure test()
begin
declare sql_error int default false;
declare continue handler for sqlexception
set sql_error = true;
start transaction;
update vendors set vendor_name = 'FedUP' where vendor_name = 'United Parcel Service';
update invoices set vendor_id = (select vendor_id from vendors where vendor_name = 'FedUP')
where vendor_id = (select vendor_id from vendors where vendor_name = 'United Parcel Service');
delete from vendors where vendor_name = 'United Parcel Service';
if sql_error = false then
commit;
else
rollback;
end if;
end //
delimiter ;
call test();