Question

Write a script that creates and calls a stored procedure named test. This procedure should use a transaction that includes the statements necessary to combine two customers. These statements should do the following: Select a row from the Customers table for the customer with a customer_id value of 6. This statement should lock the row so other transactions can’t read or modify it until the transaction commits, and it should fail immediately if the row is locked from another session. Update the Orders table so any orders for the selected customer are assigned to the customer with a customer_id value of 3. Update the Addresses table so any addresses for the selected customer are assigned to the customer with a customer_id value of 3. Delete the selected customer from the Customers table. If these statements execute successfully, commit the changes. Otherwise, rollback the changes.

Answer 1

Answer:

Check the explanation

Explanation:

use `my_guitar_shop`;

#delete the procedure test if it exists.

DROP PROCEDURE IF EXISTS test;

DELIMITER //

CREATE PROCEDURE test ()

BEGIN

   #declare variable sqlerr to store if there is an sql exception

  declare sqlerr tinyint default false;

   #declare variable handler to flag when duplicate value is inserted

  declare continue handler for 1062 set sqlerr = TRUE;

   #start transaction

   start transaction;

  delete from order_items where order_id in

      (select order_id from orders where customer_id=6);

  delete from orders where customer_id=6;

  delete from addresses where customer_id=6;

  delete from customers where customer_id=6;

 

   if sqlerr=FALSE then

      commit;

        select 'Transaction Committed' as msg;

  else

       rollback;

       select 'Transaction rollbacked' as msg;

   end if;

end //

delimiter ;

call test();