Assignment 10

More Stored Procedures and Triggers

Stored Procedure

Create a stored procedure to add a new customer. To do this add person, vehice and registerd customer info. First check to maks sure the customer does not already exist. If they do just print out a message saying they already exist; If they don't exist insert into all the tables necessary. Put the inserts in a transaction and a try catch. You want all the inserts to happen or none of them.

Remember you will need to find the newly generated person key. You can use @@identity or IDENT_Current to find it.

The procedure will create a temporary password that uses the first letter of the first name, the whole last name, and the first 3 characters of their vehicle License number. If you feel clever you can seperate this into a function and then call the function in the stored procedure

Use the procedure to enter a customer

Try Entering the same customer. You should get the message that the customer exists

---

Trigger

Create a trigger for insert on the table CustomerSurvey. If a customer rates the service at 2 or less, copy the record to a table called "Problem". This table should have the same structure as the CustomerSurvey table. The trigger should check to make sure the customer survey table exists. If it doesn't it should create it.

Enter a survey record with a rating of 1 and see if the trigger works. Also enter one with a rating of 3 to make sure that it isn't written to the problem table.

What I am Looking for

The stored procedure code will be worth 5 points
1 point for checking to see if the customer exists
1 point for creating a transactions
1 point for inserting the records
2 points for creating the temporary password

The trigger will also be worth 5 points
1 point for checking that the table exists
1 point for creating the table
1 point for checking the value of the rating
1 point for copying the results to the new table
1 point for a working trigger

To Turn in

Copy the code for the trigger and the stored procedure into a google doc and share with spconger@gmail.com