Corrección de Trigger y lectura de XML

 Jarod Cervantes Gutiérrez,    1:00-3:00, 4:00 - 6:30    (4 horas y 30 min)

Primero se estuvo viendo que faltaba en los SP y tablas debido a que se tuvo que recuperar un back up de la base de datos de hace días.

Uno vez se reviso se siguió con el SP de la simulación, solo se le agrego que cargara beneficiarios y movimientos. También se creo de nuevo el Trigger de Cuenta que debe crear un estado de cuenta.

Se probó de nuevo el Trigger y solo estaba funcionando para la ultima cuenta de cada nodo de Fecha. Así que se decidió hacer unas iteraciones a las Cuentas, tal cual se hace para las Fechas quedando así el código.

SELECT @LenCuenta = COUNT(O.f.value('@ValorDocumentoIdentidadDelCliente','INT'))
FROM @DocumentoXML.nodes('Operaciones/FechaOperacion[sql:variable("@iterador")]/Cuenta') AS O(f)

                --cuenta la cantidad de Cuentas Ahorro que hay en un nodo de Fecha

SET @IteradorCuenta=1
WHILE @IteradorCuenta<=@LenCuenta--para ir iterando sobre cada cuenta en cada nodo fecha
BEGIN
INSERT INTO [dbo].[CuentaAhorro]
(
 [IdPersona]
,[IdTipoCuenta]
,[NumeroDeCuenta]
,[FechaCreacion]
,[Saldo]
,[InsertAt]
,[UpdateAt]
,[InsertBy]
,[UpdateBy]
,[InsertIn]
,[UpdateIn]
)
SELECT
(
SELECT 
P.Id
FROM 
[dbo].[Persona] P
WHERE 
C.value('@ValorDocumentoIdentidadDelCliente', 'INT') = P.ValorDocumentoIdentidad
) AS 
 [IdPersona]
, C.value('@TipoCuentaId', 'INT')
, C.value('@NumeroCuenta', 'INT')
, @FechaOperacion --Fecha de creacion
, 0 --Saldo inicial
, @FechaOperacion AS [InsertAt] 
, GETDATE() AS [UpdateAt] --obtiene la fecha del dia de actualizacion
, CURRENT_USER AS [InsertBy] 
, CURRENT_USER AS [UpdateBy] --Usuario actual de la BD
, 'Azure-server' AS [InsertIn] 
, 'Azure-server' AS [UpdateIn] --string de prueba
FROM 
@DocumentoXML.nodes('Operaciones/FechaOperacion[sql:variable("@iterador")]/Cuenta[sql:variable("@IteradorCuenta")]') AS S(C)--acá se usa el iterador de fechas y el de cuentas

SET @IteradorCuenta = @IteradorCuenta+1

END

Se decidio iterar sobre las cuentas para que el Trigger se activase cada que se creaba una cuenta y sí funciona, pues al final de la ejecución crea un Estado de Cuenta para Cuenta Ahorro.

Luego se realizó la inserción de Movimientos, se vio que se necesitaba el Id de Estado de Cuenta, así que se creó una Función que realiza ese trabajo. Esta función recibe el número de cuenta y la fecha de creación.

        DECLARE @outIdEstado INT
DECLARE @idCuentaAhorro INT,
    @idEstado INT

         --mapea el id de cuenta para luego buscarlo en Estado de Cuenta
SELECT 
@idCuentaAhorro = [Id]
FROM 
[dbo].[CuentaAhorro]
WHERE 
@inCodigoCuenta = [NumeroDeCuenta]

        --mapea el id de estado con el idCuenta, y que la fecha este entre la fecha incio y fecha final
SELECT
@idEstado = [Id]
FROM
[dbo].[EstadoCuenta]
WHERE
@idCuentaAhorro = [IdCuentaDeAhorro] AND
@inFecha <= [FechaFinal] AND
@inFecha >= [FechaInicio]

SELECT @outIdEstado = @idEstado
RETURN @outIdEstado

Y se creó otra función que calcula el nuevo saldo, que recibe el numero de cuenta, el tipo de movimiento y el monto: 

    DECLARE @outNuevoSaldo MONEY,

@montoCuenta MONEY

--mapeo la cuenta con el codigo de cuenta y obtengo el saldo

        --usa el saldo, este se va actualizando con un trigger llamada cada que se crea un movimiento

SELECT 

@montoCuenta = [Saldo]

FROM 

[dbo].[CuentaAhorro]

WHERE 

@inCodigoCuenta = [NumeroDeCuenta]

--de acuerdo al tipo de movimiento se suma o se resta

IF ( @inTipoMovimiento >= 1 AND 

@inTipoMovimiento <= 3)

SET @outNuevoSaldo = @montoCuenta - @inMonto

ELSE 

SET @outNuevoSaldo = @montoCuenta + @inMonto

    RETURN @outNuevoSaldo

Después se vio que que para el nuevoSaldo en Movimiento se necesitaba el saldo de CuentaAhorro, y además que por cada movimiento se actualizará el saldo de Cuenta. Así que se decidió hacer un Trigger que actualice el Saldo en CuentaAhorro, y para que el trigger fuese llamada se realizó una iteración igual que en CuentaAhorro.

SELECT @LenMov = COUNT(O.f.value('@Tipo','INT'))
FROM @DocumentoXML.nodes('Operaciones/FechaOperacion[sql:variable("@iterador")]/Movimientos') AS O(f)

SET @IteradorMov=1

WHILE @IteradorMov<=@LenMov--para ir iterando sobre cada movimienot en cada nodo fecha
BEGIN

INSERT INTO [dbo].[Movimiento]
SELECT  
                    ...
        ,dbo.FuncObtenerIdEstado (d.value('@CodigoCuenta', 'INT'), @FechaOperacion)--
                        ...
                        ,dbo.FuncObtenerNuevoSaldo (d.value('@CodigoCuenta', 'INT'), d.value('@Tipo', 'INT'), d.value('@Monto', 'MONEY') )

FROM 
@DocumentoXML.nodes('Operaciones/FechaOperacion[sql:variable("@iterador")]/Movimientos[sql:variable("@IteradorMov")]') AS t(d);

    SET @IteradorMov = @IteradorMov+1

    END

Luego está el Trigger que va actualizando la cuenta de Ahorro: 

        DECLARE @nuevoSaldo INT,

@idCuenta INT

SELECT TOP 1

@nuevoSaldo = inserted.NuevoSaldo,

@idCuenta = inserted.IdCuentaAhorro

FROM inserted

UPDATE 

dbo.CuentaAhorro 

SET 

Saldo = @nuevoSaldo

WHERE

[Id] = @idCuenta

Lo actualiza con NuevoSaldo de Movimiento puesto que este se está actualizando a través de una función.

Se usaron funciones porque se investigo y se vio que no se podian llamar SP dentro de SELECT.

Para la creación de Trigger se usó como referencia la documentación de Microsoft :

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15#:~:text=A%20trigger%20is%20a%20special,manipulation%20language%20(DML)%20event.&text=SQL%20Server%20lets%20you%20create%20multiple%20triggers%20for%20any%20specific%20statement.

Al igual que para la creación de las funciones :

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver15#general-remarks