1. Write a script that includes these statements coded as a transaction: INSERT

ID: 3842288 • Letter: 1

Question

1. Write a script that includes these statements coded as a transaction:

INSERT Orders

VALUES (3, GETDATE(), '10.00', '0.00', NULL, 4,

'American Express', '378282246310005', '04/2013', 4);

SET @OrderID = @@IDENTITY;

INSERT OrderItems

VALUES (@OrderID, 6, '415.00', '161.85', 1);

INSERT OrderItems

VALUES (@OrderID, 1, '699.00', '209.70', 1);

Here, the @@IDENTITY variable is used to get the order ID value that’s automatically generated when the first INSERT statement inserts an order.

If these statements execute successfully, commit the changes. Otherwise, roll back the changes.

Answer:

The script that includes the given statements coded as a transaction is given as below :

DELIMITER //

PROCEDURE order()
BEGIN
DECLARE error_in_sql INT DEFAULT FALSE;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET error_in_sql = TRUE;

START TRANSACTION;

INSERT INTO orders VALUES
(DEFAULT, 3, NOW(), '10.00', '0.00', NULL, 4,
'American Express', '378282246310005', '04/2013', 4);

SELECT LAST_INSERT_ID()INTO order_id;

INSERT INTO order_items VALUES
(DEFAULT, order_id, 6, '415.00', '161.85', 1);

INSERT INTO order_items VALUES
(DEFAULT, order_id, 1, '699.00', '209.70', 1);

IF error_in_sql = FALSE THEN
COMMIT;
SELECT 'Transaction was committed.';
ELSE
ROLLBACK;
SELECT 'Transaction was rolled back.';
END IF;
END //

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.