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)
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.