Unit 5 SQL Assignment Project
10/14/14
Table of Contents
Abstract 3
Part 1 4
Part 2 7
References 14
Abstract
This research assignment involved various steps of implementing transactions, error handling, and dynamic SQL. The purpose of this research was to compare and contrast several procedures, such as autocommit, DML trigger with stored procedures, DML trigger with foreign keys or a check constraint. This document also will help to explain why READ COMMITTED isolation allows more concurrency than SERIALIZABLE. This research concluded with creating routines, ensuring tables were archived before deleting them by creating an INSTEAD OF trigger. The most challenging part of this assignment was creating a stored procedure that would INSERT an employee in the table. The INSERT executed successfully, however the testing of the stored procedure would not execute. The solution to the problem was found with the help of the Tech Support, it involved typing errors that were not matching the INSERT.
Unit 5 SQL Assignment Research
Below are the answers to the questions for Unit 5, it contains Part 1 and Part 2 answers as required. This assignment demonstrates the research of implementing transactions, error handling, and dynamic SQL queries.
Part 1
a. The SQL Server for user transactions has three modes that are used for transactions (Gan, & Sarka, 2012, pp. 411-418). The three modes are:
1) Autocommit
2) Implicit transaction
3) Explicit transaction
The autocommit mode is the default transaction management mode, which does not require any transactional commands. Autocommit is the default operation of the SQL Server, and basically it commits the changes once the statement is executed (Gan, & Sarka, 2012, p. 416). If the statements are successful in the context of a transaction will be automatically committed by autocommit or it will automatically roll back if the statement is not successful and fails. The implicit...