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

Question

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.

Explanation / Answer

set TERMOUT OFF
set feedback off
set pages 50000
set lin 500
set array 1000
set trimspool on
set head off
SET NEWPAGE NONE
set verify off
COLUMN dt NEW_VALUE Z
SELECT TO_CHAR(SYSDATE,'YYYYMMDD_HH24MMSS') dt FROM sys.dual;
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);
Spool off

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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