Trigger for Updation

Question

I've 2 tables

LOAN

LOANID, LOANTYPE, LOANAMOUNT,
NOOFINSTALLMNTS, REPAYMENTSTARTDATE, LOANSTATUS,

LOANINSTALLMENT

INSTALLMENTID, LOANID, INSTALLMENTAMT, INSTALLMENTNO, INSTALLMENTDUEDATE, INSTALLMENTPAYEDYN

When the value in 'INSTALLMENTPAYEDYN' field of all rows having same 'LOANID' becomes 'Y' in 'LOANINSTALLMENT'table, i need to update table 'LOAN' and set its 'LOANSTATUS' field value as 'CLOSED' for that particular 'LOANID'. How to write a trigger for this?

Answer 1

Hi Sanu,

I think you are updating status like that

Update [LOANINSTALLMENT] set INSTALLMENTPAYEDYN='Y' where Loanid=1


so don't worry, try this.

Create  TRIGGER [dbo].[LoanStatusUpdate] ON [dbo].[LOANINSTALLMENT]
AFTER UPDATE
AS
DECLARE @LoanID int
SET @LoanID=(select Top 1  LOANID from inserted)
if not exists(Select top 1 LoanID from LOANINSTALLMENT where INSTALLMENTPAYEDYN <> 'Y' and LoanId=@LoanID)
BEGIN
    update LOAN
    set  LOANSTATUS='CLOSED' where LoanId=@LoanID
   
END

Answer 2

Thank you sooooooooooo much, Manish.
It worked..

Answer 3

Same error again....

Answer 4

try this

Create TRIGGER [dbo].[LoanStatusUpdate] ON [dbo].[LOANINSTALLMENT]
AFTER UPDATE
AS
DECLARE @LoanID int
SET @LoanID=(select  LOANID from inserted)
if not exists(Select top 1 LoanID from LOANINSTALLMENT where INSTALLMENTPAYEDYN <> 'Y' and LoanId=@LoanID)
BEGIN
    update LOAN
    set  LOANSTATUS='CLOSED' where LoanId=@LoanID
   
END

Answer 5

Thank you so much, Manish

I executed the trigger. And when the INSTALLMENTPAYEDYN field of all records having same loanid is given value 'Y', i get an error like

"Msg 512, Level 16, State 1, Procedure LOANSTATUSUPDATE_TRIGGER, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Answer 6

Hi Sanu

try this

Create TRIGGER [dbo].[LoanStatusUpdate] ON [dbo].[LOANINSTALLMENT]
AFTER UPDATE
AS
DECLARE @LoanID int
SET @LoanID=(select  LOANID from inserted)
if not exists(Select LoanID from LOANINSTALLMENT where INSTALLMENTPAYEDYN <> 'Y' and LoanId=@LoanID)
BEGIN
    update LOAN
    set  LOANSTATUS='CLOSED' where LoanId=@LoanID
   
END