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?
Blocked AccountPosted Nov 29, 2012, 3:38 AM
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
SanuPosted Nov 29, 2012, 3:47 AM
It worked..
SanuPosted Nov 29, 2012, 3:15 AM
Blocked AccountPosted Nov 29, 2012, 3:03 AM
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
SanuPosted Nov 29, 2012, 2:40 AM
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."
Blocked AccountPosted Nov 29, 2012, 2:22 AM
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