IT452 Unit 8 Programmable Objects II

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:
USE tempdb;
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

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

Calculate your essay price
(550 words)

Approximate price: $

Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)
Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more