Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Proc. to return number of customers; audit table.

Author  Topic 

teh
Starting Member

1 Post

Posted - 2013-04-25 : 10:36:24
Hello, I've recently been given a task to set up stored procedures, queries, audit tables and few more (not work related). I've managed to complete some of these, however there's some requirements left that im stuck with. I would appreciate any help on how to complete these:

quote:
1. Create a basic stored procedure called TotalCus that will return the number of customers in tblCustomer. Show how to run the stored procedure.


quote:
2. There have been some complaints about the number of items delivered to customers because the quantity field has been updated and changed on orders. Therefore the management would like an audit table to track who and when changes are made.

Create an audit table to track users (username) who update the tblSOLINE table and the time that they do an update. Create a trigger that fires when an update is made. You do not need to track inserts or deletes.

You need to provide the create table code; the create trigger code; the ‘update a record in tblSOLine’ code; the ‘display the records in the audit table’ code to show that the trigger has worked; the ‘Insert a new record to tblSOLine’ code; Show this has not fired the trigger.

Ensure that you reset any changes in tblSOline before continuing.


quote:
3. Create a stored procedure to return the total sales value for a given customer (customer name) based on the products ordered, quantity ordered and selling price. This will require a number of joins to get the data from several tables, an expression to total the selling price, and a stored procedure that accepts an appropriate parameter. Run the stored procedure to return the total spend for Mark Jacobi.


quote:
4. Create an INSTEAD OF trigger that will fire when a delete statement is run against the Customer table. Instead of deleting the customer the trigger should update a new STATUS column in customer from null to 1 and insert into a new audit table the customerid, name , address, user and time of the attempted deletion. The trigger should also provide a message to say the ‘customer status has been updated in tblCustomer and logged in tblCusArchAudit’. Demonstrate the trigger. The trigger must work correctly to gain the marks. You will need to research how to modify your table structures before creating the trigger.


I am including the table relationship diagram to give you an insight into the database:



Once again, I would really appreciate the solutions to these. Thanks for any help in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-25 : 12:33:09
you mean you want someone to write all these code for you? What would be more sensible would be you making an attempt by refering to MSDN/books online and posting only issues you face. Otherwise I dont think too many people would have time to help with all these and also you wont benefit anything from spoonfeeding

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -