Stored Functions/Procedures/Triggers

Objectives

• Create a scalar function
• Create a stored procedure
• Declare Parameters
• Declare variables
• use transaction
• Use try catch

Description

For this assignment we will create a scalar function that creates the tenanID, we will create [x] stored procedures and [x] triggers. All the excercises will use the AptManager Database

To Do

1. Create a function that given the firstname and lastname of a tenant will create the tenant id. It should take 3 parameters: lastname, firstname, and increment (the '01' or '02') It should return a char(10). remember all you learned about concatination and substrings, trimming and lower functions.

2. Create a stored procedure that takes an apartment number for a parameter and returns the current tenant and lease information for that apartment.

3. This is a big one. You might consider doing it in iterations, also it can help to diagram the steps required to complete this procedure. Create a stored procedure that enters a tenant and a new lease. You should enter all the information needed for tenant and lease. Check to see if the tenant exits, if he or she does get their current lease and update it to make tne enddate of the lease be today's lease then enter the new lease information. If the tenant does not exist enter the tenant and the lease information. Use the function you created for 1 to get the tenantID and also the function we did in class to create the leasenumber

/* this function creates the leaseid given two 
parameters the startdate and the apartment number. */
create function func_LeaseID 
(@startDate Datetime, @apt char(3)) --function parameters
Returns char(10) --return type
WITH EXECUTE AS CALLER --executes for the caller in a select
As -- begin function definition
Begin --start function body
Declare @leaseNumber Char(10) --declare an internal variable
Select @Leasenumber=rtrim(cast(month(@startdate)as char))
	+ rtrim(cast(day(@startdate)as char))
	+ substring(cast(year(@startdate)as char),3,2)
	+ @apt --us the functions we have learned to get the pieces
--of the lease number
return (@leasenumber) -- return the results
End --end the function body

Use transactions and use try catch to for error trapping.

For a test create a new tenant for Apt 302. Choose one one of the exisiting tenants to test it with also

4. Create a trigger on insert for the RentHistory table that will report any rents that are under the amount due. Write them into table underrents. Check if the table exists. If if doesn't create it. Test it by doing an insert into renthistory and paying less than the amount due. the select from the underrents table

To turn in

Turn in the code and the test SQL and results