IT452 Unit 8 Programmable Objects II
1. Write a stored procedure called sp_GetCompanyName. This procedure requiring a join should get the customer name (not individual) and the city the customer gets items shipped to for those customers that have items shipped to Madrid (this is a hint on the table it should be joined with in the query). Execute the stored procedure and submit results with query.
Returns 2 rows
2. Write a stored procedure called sp_GetEmployees to dynamically retrieve the city that each employee lives in when it is executed. In addition, please return the lastname and firstname. Execute the procedure and submit the code and results for employees living in London.
Returns 4 rows
3. [TSQLFundamentals2008 and tempdb] Scenario: You want to test a stored procedure that will delete the oldest orders. You will run your tests in the tempdb database.
a. Create an exact copy of the TSQLFundamentals2008.Sales.Orders table in the tempdb database (as table tempdb.dbo.Orders) by using SELECT INTO.
The tempdb.dbo.Orders table should have 830 rows.
b. In tempdb, create a stored procedure named dbo.pDeleteOldestOrders that (1) determines the earliest year and month of orderdate values in the Orders table, and then (2) deletes all orders with an orderdate in that year and month. (Hint: Use YEAR and MONTH functions in subqueries to determine the year and month of the oldest order. Place these values in variables, then use the variables in the WHERE clause of your DELETE statement.)
Should show 22 rows affected
4. Use Northwind (create one if one does not exist) and Master Create a rollback (prevent changes if attempt to change occurs and prints a system message and the custom message using RAISERRORThese Procs may not be altered or dropped!) Trigger called NoTouchDaProc that prevents any stored procedure from being altered or dropped in the database (Hint: in the database tells you which type of trigger).
Submit code and printscreen and drop the trigger after getting results.
5. Use the tempdb and using SELECT INTO create a table called Person using all of the data from the Adventureworks.Person.Contact Table.
You should have 76 rows of individuals with the LastName Carter and 88 rows of individuals with the LastName Johnson before you begin.
1. Create a Trigger called PersonTrigger that works on data after it is inserted into the table and checks for a change to the LastName field. If there is a change to the LastName field print to the screen ‘You might have modified the LastName column’. Otherwise have it print ‘The LastName column is untouched.’ (Hint: IF Else would work here). Make print screens of successful completion and copy and paste your code into your submission.
2. Change the LastName of the person with the contactID of 32. This should update 1 row and fire the PersonTrigger with the correct message. Make print screens of successful completion and copy and paste your code into your submission.
3. Alter the PersonTrigger to check the Inserted and Deleted tables (those that get created when data is inserted or deleted for tables/rows included within a trigger) so that that the trigger now looks in the Inserted and Deleted tables for data updated in the LastName field of the person table. Here is what the SELECT clause should look like: SELECT D.LastName + ‘ changed to ‘ + I.LastName. Complete the balance of the query including the required JOIN between Inserted and Deleted. At this point you should now have 89 people with the LastName Johnson. Make print screens of successful completion and copy and paste your code into your submission.
4. Change anyone with the LastName of Carter to Johnson. This should fire the altered trigger and use the new SELECT clause to report the results to the screen with the number of rows changed to Johnson. 89 rows should be changed from Carter to Johnson. Make print screens of successful completion and copy and paste your code into your submission.
6. [TSQLFundamentals2008 and tempdb] Scenario: You want to archive deleted orders. You will create a DML trigger to accomplish writing all columns of a deleted order and the time the deletion occurred to an OrdersArchive table. You will test this trigger in the tempdb database.
a. “Reset” the dbo.Orders table in the tempdb database by dropping the table (DROP TABLE tempdb.dbo.Orders; ) and then re-running your code from step a. of the previous problem.
b. Create the tempdb.dbo.OrdersArchive table by executing the following code:
CREATE TABLE [dbo].[OrdersArchive]
[orderid] [int] NOT NULL CONSTRAINT PK_OrdersArchive PRIMARY KEY,
[custid] [int] NULL,
[empid] [int] NOT NULL,
[orderdate] [datetime] NOT NULL,
[requireddate] [datetime] NOT NULL,
[shippeddate] [datetime] NULL,
[shipperid] [int] NOT NULL,
[freight] [money] NOT NULL,
[shipname] [nvarchar](40) NOT NULL,
[shipaddress] [nvarchar](60) NOT NULL,
[shipcity] [nvarchar](15) NOT NULL,
[shipregion] [nvarchar](15) NULL,
[shippostalcode] [nvarchar](10) NULL,
[shipcountry] [nvarchar](15) NOT NULL
[whenarchived] [datetime] NOT NULL
CONSTRAINT DF_WhenArchived DEFAULT CURRENT_TIMESTAMP
c. Create a trigger named dbo.tdArchiveOrders that writes rows deleted from dbo.Orders to the dbo.OrdersArchive table. Note that the when archived column should contain the current datetime value for the deleted row.
d. Execute the dbo.pDeleteOldersOrders stored procedure that you wrote in the previous problem.
In your Assignment document show:
• The code that creates the dbo.tdArchiveOrders trigger
• The contents of the messages tab of the query window after executing the stored procedure