01/01/1400 02/02/1405 Balance Write a Procedure (called add new to add new clien
ID: 3837051 • Letter: 0
Question
01/01/1400 02/02/1405 Balance Write a Procedure (called add new to add new client, this procedure add also new account for this client, the acc client with 111 and increased by 1. no column will start the balance will be 0 for each new client. Add an exception s error message "client id is already used" if there is duplicated client id. Using (DUP vALON INDEX) write a deposit procedure that receive client id and amount to add this amount to clients balance and set the time of this transaction (as the last transaction time) write a withdrawal procedure that receive client id and amount to deduct this amount from this clients balance if his balance is covered otherwise show error message "sorry your balance is not enough also set the time of this transaction (as last transaction Write a transfer procedure that have 3 parameters: clients' id for both sender and receiver also the amount to deduct this amount from the sender and add it to the receiver. Make sure that the sender balance is enough. write a trigger to show old and new balance after each deposit, withdrawal or transfe write a procedure called L-balance to display names and balances for all clients if the balance is less than entered a (by user). Also show how many are they. o Add an exception shows error message "no one is less than (entered amount if there is no one found Using (NO DATA FoUND), showthetthetehentaauw aloneweaterthmmmttt, if the entered value is less than tht, finally sho ERROR' for another exceptions. s hou theam umt my sts bes rater tn en o write a display info procedure to show all information of specific client.Explanation / Answer
1 . add_new_client
ALTER PROCEDURE add_new_client(@cd int,@name char(40),@dob varchar,@bal money)
AS
DECLARE @error Integer
SET @error=0
SET TRANSACTION Isolation Level SERIALIZABLE
BEGIN TRANSACTION
BEGIN
IF EXISTS (SELECT COUNT(*) FROM clients WHERE c_id = @cd)
SET @error=@@error
IF @error <> 0
BEGIN
--Raise an error and return
RAISERROR('client id is already used',16,1)
ROLLBACK TRANSACTION
End
Else
Begin
insert into clients(c_id,c_name,dob) values(@cd,@name,@dob)
insert into accounts(balance,c_id) values(@bal,@cd)
End
COMMIT TRANSACTION
END
2.deposite,withdraw
alter proc deposite(@no int,@bal money)
as
begin
update accounts set balance=balance+@bal ,trans_time=getdate() where c_id=@no
end
alter proc withdraw(@no int,@bal money)
as
begin
update accounts set balance=balance-@bal ,trans_time=getdate() where c_id=@no
end
execute deposite 1,200
execute withdraw 1,100
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.