sql use cursor to perform an operation between two tables

Question

sql use cursor to perform an operation between two tables

Navigation

#1 by (2 votes)

2

I have been asked to create a trigger which realizes an update of the balance field of the loan table, which is calculated by adding all the payments made in the column amount in the payment tablePrestamos, so I must do an update of all the Loans and your balance to pay the tables have these characteristics:

create table prestamo(
idCuenta int identity(1,1),
cantidadCuotas int not null,
monto decimal(13,2) not null,
saldo decimal(13,2) not null,
constraint pk_idprestamo primary key(idCuenta)

);

insert into prestamo values(60, 500000, 500000),(60, 5000000, 5000000), (60, 600000, 600000),(60, 1000000, 500000)

create table pagoPrestamo(
idPago int identity(1,1),
idCuenta int not null,
monto decimal(13,2) not null,
fecha date not null,
constraint pk_idpago primary key(idPago),
constraint fk_idcuent foreign key (idCuenta) references prestamo(idCuenta) 
);
insert into pagoPrestamo values(2,5000,'03/03/2018'),(4,10000,'04/13/2018'),(6,15000,'02/23/2018'),(8,45000,'04/04/2018'),(1,50000,'06/05/2018')

The trigger I created is this:

create trigger tr_pago_prestamo on pagoPrestamo
after insert
as
Begin

declare @idPrestamo int
declare @prestamoMonto decimal(13,2)
declare @pagoMonto decimal(13,2)
declare @saldo decimal(13,2)

select @pagoMonto =  Sum(monto) from pagoPrestamo where pagoPrestamo.idCuenta = @idPrestamo
select @prestamoMonto =  monto  from prestamo where idCuenta = @idPrestamo
select @saldo = @prestamoMonto - @pagoMonto

declare logica cursor for select prestamo.idCuenta, prestamo.monto,        prestamo.saldo from prestamo inner join pagoPrestamo on prestamo.idCuenta =   pagoPrestamo.idCuenta

open logica


while (@@FETCH_STATUS = 0 )
begin
update prestamo set prestamo.saldo = prestamo.monto - @pagoMonto
fetch next from logica into @idPrestamo, @prestamoMonto, @saldo

end
close logica
deallocate logica
end

The problem is when I execute the trigger, it puts the account's balance in null and I do not know why. I am new to using the cursor, thank you very much for your help

sql cursor

asked by Barly Espinal 27.11.2018 в 08:16

source

1 answer

Error in a List Problem with class extends in PHP

score 2 · Accepted Answer

You have several problems:

You have already been mentioned, you are doing update prestamo set prestamo.saldo = prestamo.monto - @pagoMonto without filter, that is, you are updating all the rows in the prestamo table, I understand that in reality what you should do is:
```
UPDATE prestamo SET prestamo.saldo = prestamo.monto - @pagoMonto
   WHERE idcuenta = @idPrestamo
```
Although, the name of the variable is not the most appropriate, it would be preferable @idCuenta
Another problem is that you are NOT doing FETCH of the cursor values at the beginning, that is, the first round of the course cycle @idPrestamo will be NULL . The skeleton of a cursor cycle is:
```
open cursor
fetch variables
while
   logica
   fetch variables
fin while
```
Once the previous points are solved, the final problem is that you end up updating a value NULL in prestamo.saldo , the table does not allow it and it is also incorrect that it is NULL this is due to the variable @pagoMonto not the you never initialize with this query: select @pagoMonto = Sum(monto) from pagoPrestamo where pagoPrestamo.idCuenta = @idPrestamo simply because @idPrestamo has no value. Also, I understand that the idea is to update the balance of the prestamo based on the pago inserted. With which I see two other problems to your trigger:
Do not read the table pagoPrestamo within the trigger, it would be correct to access the pseudo table INSERTED that represents the updated or inserted values of the trigger table. There is also DELETED to read the previous values if you need them. You also need to read each of the payments, that is, the amount you have to obtain in the same cursor that runs through each of the inserted payments.
The other problem is logic, this query, this: set prestamo.saldo = prestamo.monto - @pagoMonto only works the first time, with a second payment no longer, the correct thing in any case is to act on the saldo , that is: set prestamo.saldo = prestamo.saldo - @pagoMonto

Summarizing the observations your cursor could look like this:

create trigger tr_pago_prestamo on pagoPrestamo
after insert
as
Begin

    declare @idCuenta int
    declare @pagoMonto decimal(13,2)

    declare logica cursor for 
    select  prestamo.idCuenta, PP.monto
        from prestamo 
        inner join INSERTED PP 
            on prestamo.idCuenta = pp.idCuenta

    open logica
    fetch next from logica into @idCuenta, @pagoMonto
    while (@@FETCH_STATUS = 0 )
    begin
        update prestamo set prestamo.saldo = ISNULL(prestamo.saldo,0) - ISNULL(@pagoMonto,0)
            where idCuenta = @idCuenta

        fetch next from logica into @idCuenta, @pagoMonto
    end
    close logica
    deallocate logica
end

Note: I also deleted all unnecessary statements and queries to make the code clearer