Practical Learning1: Using trigger and cursor (Loan System)

1. Practical Learning1: Using trigger and cursor (Loan System)

2. sUmebIk Table: Customers edIm,IbBa¢ÚlnUvTinñn½ydUcxageRkam³

3. Loan: Insert Event • Trigger xageRkamenH )ansresrelI table: Loans enAelI Event: Insert edIm,I ³ • cab;yktMélrbs; Amount eGaybgðajkñúg Collumn: Balance • bUkbEnßmTwkR)ak;CMBak;srub (AmountBalance) én Table : Customers • krNIEdlelakGñkeFVIkarbBa¢ÚlTwkR)ak;xI¢ rbs; Customer kñúg Table: Loans Create Trigger [Loan_insert] on [dbo].[Loans] For Insert As DECLARE @AmountLoan int Select @AmountLoan=Amount from Inserted where CustomerID=(Select CustomerID from inserted) Begin Update Loans set Balance= Amount where LoanID=(Select LoanID from inserted) Update Customers set AmountBalance=AmountBalance+@AmountLoan where CustomerID=(Select CustomerID from Inserted) End

4. Testing your trigger sUmebIk Table: Loans edIm,IbBa¢ÚlnUvTinñn½ydUcxageRkam³ Result in column: AmmountBalance

5. Loan: Delete Event • Trigger xageRkamenH )ansresrelI table: Loan enAelI Event: Delete edIm,I ³ • eFIVkar pøas;bþÚrTwkR)ak;srubTwkR)ak;CMBak; Collumn: AmountBalance én table: Customers krNIEdlelakGñkeFIVkar lub Record én Table: Loan Create Trigger [Loan_Delete] on [dbo].[Loans] For Delete As Update Customers set AmountBalance=AmountBalance-(select Amount from deleted) Where CustomerID=(select CustomerID from deleted)

6. Testing your trigger sakl,glub record TI2 Result in column: AmmountBalance

7. Loan: Update Event ALTER TRIGGER [Loan_Update] ON [dbo].[Loans] FOR UPDATE AS DECLARE @AmountNew int, @AmountOld int ---Assign Value to Variable Select @AmountNew=Amount from Inserted where CustomerID=(Select CustomerID From Inserted) Select @AmountOld=Amount From Deleted Where CustomerID=(Select CustomerID From Deleted) if Update(Amount) or Update(CustomerID) Begin Update Loans set Balance=Amount Where LoanID=(Select LoanID from Inserted) Update Customers set AmountBalance=(AmountBalance+@AmountNew)-@AmountOld Where CustomerID=(Select CustomerID From Inserted) End

8. Create store procedure to deduct customer loan when you insert data to table: Loans Create proc [dbo].[sp_balanceLoan_Insert] @CustID int, @AmountPay money As Declare CurPay Cursor for select LoanID,Amount, Balance from Loans Where CustomerID=@CustID and balance>0 order by DateLoan Asc Begin Declare @LoanID int, @Amount money, @Balance money Open CurPay Fetch CurPay into @LoanID,@Amount, @Balance while @@fetch_Status=0 Begin if @AmountPay>@Balance Begin Set @AmountPay=@AmountPay-@Balance Update Loans set Balance=0 Where LoanID=@LoanID end else begin Set @Balance=@Balance-@AmountPay Update Loans set Balance=@Balance Where LoanID=@LoanID set @AmountPay = 0 break end Fetch Curpay into @LoanID,@Amount, @Balance end Close CurPay Deallocate CurPay end

9. Create store procedure to deduct customer loan when you Delete data from table: Loans Create proc [delPayment] @custId int, @AmountPay money As begin declare @loanId int, @amount money, @balance money declare curLoan Cursor for select loanId, amount, balance from loans where customerId = @custId order by dateLoan desc Open curLoan Fetch curLoan into @loanId, @amount, @balance While @@fetch_Status=0 Begin if @AmountPay>=(@amount-@balance) begin Update loans set balance = amount where loanId = @loanId set @AmountPay = @AmountPay - (@amount - @balance) end else begin Update loans set balance = balance + @AmountPay where loanId = @loanId set @AmountPay = 0 end fetch curLoan into @loanId, @amount, @balance End close curLoan Deallocate curLoan end

10. Execute store procedure in trigger Trigger xageRkamenH )ansresrelI table: Loan_payment enAelI Event: Insert Create Trigger [Payment_Insert] On [Loan_Payment] For INSERT As Declare @CustID Int, @custPay Money Begin Select @custPay=Amount, @CustID=CustomerID From Inserted Where CustomerID=(Select CustomerID from Inserted) Update Customers set AmountBalance=AmountBalance-@custPay where CustomerID=(Select CustomerID from Inserted) Exec [sp_balanceLoan_Insert] @CustID, @custPay End Call store procedure

11. Testing your trigger , call store procedure • sakl,g bBa¢ÚlTinñn½ybEnßmkñúg Table: Loans dUxxageRkam³ • sakl,g bBa¢ÚlTinñn½ykñúg Table: Loan_payment dUxxageRkam³ Result in column: Balance

12. Trigger On Loan_Payment: Delete Event Trigger xageRkamenH )ansresrelI table: Loan enAelI Event: Delete Create Trigger [LoanPayment_Delete] on [dbo].[Loan_Payment] For Delete As declare @custId int, @amountPay money select @custId=CustomerID, @amountPay=amount from deleted Begin Update Customers set AmountBalance=AmountBalance+@amountPay Where CustomerID=@custId exec delPayment @custId,@amountPay end Call store procedure

13. Practical Learning 2: Using Trigger ( Tennis club database)

14. Players: Event Insert Problem: Create the trigger that update the CHANGES table automatically as new are added to the PLAYERS table. Create Trigger [Insert_Player] on [Players] For Insert As Declare @playerID int begin Select @playerID=PlayerNo from Inserted Insert into Changes([user],Cha_time,cha_pno, cha_type,cha_pno_new) Values(user,GetDate(),@playerID,'I',null) end

15. Players: Event Delete Problem: Create the trigger that update the CHANGES table automatically when rows from the PLAYERS table are removed. Create Trigger [Delete_Player] on [Players] For Delete As Declare @playerID int begin Select @playerID=PlayerNo from Deleted Insert into Changes([user],Cha_time,cha_pno, cha_type,cha_pno_new) Values(user,GetDate(),@playerID,'D',null) end

16. Players: Event Update Problem: Create the update_player trigger that updates the CHANGES table automatically if the LeagueNo column is CHANGED. ALTER Trigger [Update_Player] on[Players] For Update As Declare @playerIDNew int,@playerIDOld int If update(LeagueNo) begin Select @playerIDNew=PlayerNo from inserted Select @playerIDOld=PlayerNo from Deleted Insert into Changes([user],Cha_time,cha_pno, cha_type,cha_pno_new) Values(user,GetDate(),@playerIDNew,'U',@playerIDOld) end

17. Penalties: Event Insert Problem: Suppose that the PLAYERS table contains a column called Sum_Penalties. This column contains for each player the sum of his or her penalties. Now we would like to create triggers that automatically keep a record of the values in this column. To this end, we have to create two triggers: Create Trigger [Sum_penalties_Insert] on [Penalties] For Insert As Declare @TotalNumber int, @PlayerNo int Begin Select @PlayerNo=PlayerNo from inserted Select @TotalNumber=sum(Amount) from Penalties Where PlayerNo=@PlayerNo Update Players Set Sum_Penalties=@TotalNumber where PlayerNo=@PlayerNo End

18. Penalties: Event Update, Delete Create Trigger Sum_penalties_Delete on Penalties For Delete, Update As Declare @TotalNumber int, @PlayerNo int If update(Amount) Begin Select @PlayerNo=PlayerNo from Deleted Select @TotalNumber=sum(Amount) from Penalties Where PlayerNo=@PlayerNo Update Players Set Sum_Penalties=@TotalNumber where PlayerNo=@PlayerNo End

19. Matches: Event Insert CREATE TRIGGER [NUMBER_OF_MATCHES_INSERT] ON [Matches] FOR INSERT AS DECLARE @PLAYERNO INT BEGIN SELECT @PLAYERNO=PLAYERNO FROM INSERTED UPDATE TEAMS SET NUMBER_OF_MATCHES= (SELECT COUNT(*) FROM MATCHES WHERE PLAYERNO=@PLAYERNO) WHERE PLAYERNO=@PLAYERNO END

20. Matches: Event Update, Delete Create TRIGGER [NUMBER_OF_MATCHES_Delete] ON[Matches] FOR Delete,Update AS DECLARE @PLAYERNO INT BEGIN SELECT @PLAYERNO=PLAYERNO FROM Deleted UPDATE TEAMS SET NUMBER_OF_MATCHES= (SELECT COUNT(*) FROM MATCHES WHERE PLAYERNO=@PLAYERNO) WHERE PLAYERNO=@PLAYERNO End

21. Removing triggers Just like any other database object, there is a drop statement for removing trigger. Drop Trigger Sum_penalties_Delete Disable/Enable trigger You can use enable or disable as code below Alter table players disable trigger Insert_player Alter table players enable trigger Insert_player