Details of this Paper

Compare and contrast autocommit mode with explicit transaction mode

Description

solution


Question

Part 1. Questions (50 points);Each answer should be about 100 to 300 words.;a. Compare and contrast autocommit mode with explicit transaction mode. (10);Autocommit;Explicit;b. Compare and contrast a DML trigger with a stored procedure. (15);Stored procedure is a code module;DML trigger is code that;c. Compare and contrast a DML trigger with either a foreign key or a check constraint. Explain why use of a constraint (if possible) is preferred to use of a trigger. (15);FK constraint prevents;CK constraint prevents;DML triggers can preven;d. Explain why READ COMMITTED isolation allows more concurrency than SERIALIZABLE. (10);READ COMMITTED locks rows and releases;SERIALIZABLE locks;The held range lock of SERIALIZABLE prevents;?;Part 2. Create Routines (50 points);a. You want to make sure that rows in the Sales.Orders table (TSQL2012 database) are archived when deleted. You have created the table Sales.OrdersArchive that has the same columns / data types as Sales.Orders, plus one additional column, Archived, of type datetime, to store the date and time the row is written to the archive table. Archived has a default value of CURRENT_TIMESTAMP.;Because of the foreign key constraint in the Sales.OrderDetails table on the orderid column, you know you cannot delete a row from Orders without first deleting all rows with the same orderid value from OrderDetails. You create a table Sales.OrderDetailsArchive that has the same columns / data types as Sales.OrderDetails, plus the Archived column of type datetime with default value CURRENT_TIMESTAMP.;To solve the deletion problem, create an INSTEAD OF trigger, Sales.tr_ArchiveOrders, that watches the Sales.Orders table for a DELETE and instead does the following;1. Copies all relevant rows from Sales.Orders to Sales.OrdersArchive;2. Copies all relevant rows from Sales.OrderDetails to Sales.OrderDetailsArchive;3. Deletes those rows from Sales.OrderDetails;4. Finally, deletes the relevant rows from Sales.Orders;If you want to create the two Archive tables to test your trigger, see the scripts in the "Unit5.Create Archive Tables.sql" file in Doc Sharing. (25);--The create trigger;CREATE TRIGGER Sales.tr_ArchiveOrders;ON Sales.Orders;INSTEAD OF DELETE;AS;-- Copy Orders rows to OrderArchive;-- Copy [OrderDetails] rows to OrderDetailsArchive;-- Delete rows from [OrderDetails];-- Delete rows from Orders;GO;?;b. In the pubs database, create a stored procedure that will INSERT an employee. You can develop the procedure in the same way as the procedure in Exercise 2 starting on page 486 is developed. Your finished procedure should have all relevant parameter testing. Only the finished CREATE PROC code should be given in your assignment document. Also show a call to the procedure that would insert an employee. (25);Before creating and testing this procedure, you should drop (delete) the employee_insupd trigger. To find this trigger, in Object Explorer, expand pubs, expand Tables, expand dbo.employee, expand Triggers.;CREATE PROCEDURE dbo.InsertEmployee;@emp_id AS CHAR(9),@fname AS VARCHAR(20),@minit AS CHAR(1),@lname AS VARCHAR(30),@job_id AS SMALLINT = 1,@job_lvl AS TINYINT = 10,@pub_id AS CHAR(4) = '9952',@hire_date AS datetime;AS;BEGIN -- start body of procedure;END, -- end procedure;GO;Attachment Preview

 

Paper#15959 | Written in 18-Jul-2015

Price : $57
SiteLock