Create a SEQUENCE named ChargeNumbers that starts at the next available chargenum for your database and increments by 1 each time the next value is pulled from the sequence.

Write a stored procedure that accepts the camperid, category, chargedate, and amount as input parameters and has one output parameter which is of type varchar2 which will contain a message.

If the camperID is not a valid camperID the output parameter should be set to 'ERROR: No such camper ID.'

If the category is not a valid category the output parameter should be set to 'ERROR: No such charge category.'

If the Amount is not in the correct range the output parameter should be set to 'ERROR: amount must be >0 and no more than $ 40.'

If the Amount would cause the total spent to be more than the budget for that camper the output parameter should be set to 'ERROR: insufficient funds.'

If there are no error conditions the stored procedure should

insert the new charge using the chargenumbers sequence to generate the chargenum

update the amount spent for that camper

if your trigger fails a rollback should be issued and if it succeeds a commit should be issued.

You can code for which error condition has occurred by looking at what constraint name is contained in the SQL error message SQLERRM or you can use if statements in your procedure code to perform the same tests as your constraints. If the former you may add a constraint to prevent spent field from being larger than budget field.

Because your procedure has an OUT parameter you will have to write a short program to test the procedure

Write a trigger such that if a category description is changed (e.g. change 'candy' to 'sweets' in the categories table) that change is propagated throughout the charges table.

Create a table named budgetchangelog that has columns for camperID, changedate, changeuser, oldamount, and newamount and write a trigger which writes to that log anytime a camper's budget is changed. You can use SYSDATE to specify the current date and time and USER to specify the logged in user who made the change.