Resolucion de problemas anteriores y algunos triggers

Este día resolví lo de XMLAntes y XMLDespues que podían ser vacíos y lo hice haciendo que los campos de XMLAntes y XMLDespues puedan contener nulos ya que no o no existe o simplemente no encontré ninguna forma de crear un XML que fuera vacío, por lo que los XML vacíos son en realidad nulos.

Además realice algunos triggers algunos trigger y pare allí ya que en esta semana estoy hasta la cebolla de trabajos.

A continuación un poco del código que se programó:

CREATE TRIGGER  [dbo].[COInsertados] ON [dbo].[CuentaObjeto]

   AFTER INSERT

AS

BEGIN

                -- SET NOCOUNT ON added to prevent extra result sets from

                -- interfering with SELECT statements.

                SET NOCOUNT ON;

                               --Variables para buscar id de otros elementos

    declare @TipoEvento nvarchar(100) = N'Insertar CO';

                declare @Numero_de_Cuenta nvarchar(50);                                                  --Numero cuenta para buscar el idUsuario

                              

                               --Elementos para el nuevo elemento

                declare @idUsuario int;    --El id del usuario

                declare @Fecha date = GETDATE();

                declare @idTipoEvento int;   --El id del tipo evento

                declare @IP int = 0;

                declare @XMLAntes xml;

                declare @XMLDespues xml;

                Select @Numero_de_Cuenta = NewCO.Numero_Cuenta,                        --Determina NumeroCuenta y idTipoEvento

                                               @idTipoEvento = TE.id                

                from inserted NewCO, TipoEvento TE

                where TE.nombre = @TipoEvento;

                Select @idUsuario = CA.idCliente                           --Id del usuario

                from CuentaAhorro CA

                where CA.Numero_Cuenta = @Numero_de_Cuenta; 

               

                declare @TablaXml TABLE (  --Tabla variable para sacar XML del evento

                               id int NOT NULL,

                               idCuenta int NOT NULL,

                               Saldo money NOT NULL,

                               Fecha_Inicio date NOT NULL,

                               Fecha_Final date NOT NULL,

                               Monto_Ahorro money NOT NULL,

                               Numero_Cuenta nvarchar(50) NOT NULL

                )

                insert into @TablaXml  --Insercion del Evento en tabla variable

                select newCo.id, newCo.idCuenta, newCo.Saldo, newCo.Fecha_Inicio,

                                               newCo.Fecha_Final, newCo.Monto_Ahorro, newCo.Numero_Cuenta

                from inserted newCo

                set @XMLDespues = (Select * from @TablaXml as TablaXml for xml Auto, ELEMENTS XSINIL);  --XML del Evento

                insert into Evento (idTipoEvento, idUser, _IP, Fecha, XMLAntes, XMLDespues)  --Insercion final al evento

                values(@idTipoEvento, @idUsuario, @IP, @Fecha, @XMLAntes, @XMLDespues);

END

Este día solo invertí el proyecto como 1,5 horas.
Tarde tanto porque al principio estaba haciendo malísimo la tabla que se usa para crear el XML

Triggers y tipo XML en SQL server

Este día investigue como trabajar con el tipo XML de SQL server ya que si bien ya había manejado XML en SQL server, lo había manejado para conseguir la información para las tablas como se ha hecho desde la primera parte de la tarea programada, pero nunca había usado el tipo XML que tiene SQL server entre sus tipos de datos y la verdad me di cuenta que sería bastante útil para el grupo de datos el crear los XML para pruebas si lo hicieran creando los XML’s con el tipo XML de SQL server.

También inicie con la creación de triggers, el problema fue que no sabía que era el campo de la tabla de Evento que decía IP, además estaba teniendo problemas con los XMLAntes y AMLDespues ya que en ciertas circunstancias estos tenían que estar vacíos y no supe cómo crear un XML vacío.

A continuación links vistos para la investigación del tipo XML de SQL server:

https://www.youtube.com/watch?v=odYrlGPDJ2g&t=2s

https://www.youtube.com/watch?v=ha3JV3OPNXg

https://www.youtube.com/watch?v=Qam1Ag9C9wY

https://www.youtube.com/watch?v=WIg64plWH80&t=312s

 

A continuación el poco código que hice ya que no sabía cómo proceder con el campo IP hasta que el profesor contestara el correo que le envié:

CREATE TRIGGER  [dbo].[BeneficiariosInsertados] ON [dbo].[Beneficiario]

   AFTER INSERT

AS

BEGIN

                -- SET NOCOUNT ON added to prevent extra result sets from

                -- interfering with SELECT statements.

                SET NOCOUNT ON;

    declare @idUsuario int;    --El id del usuario

                declare @Numero_de_Cuenta nvarchar(50);  --Numero cuenta para buscar el idUsuario

                declare @Fecha date = GETDATE();

                declare @TipoEvento nvarchar(100) = N'Insertar Beneficiario';

                declare @idTipoEvento int;   --El id del tipo evento

                declare @IP int = 0;

                declare @XMLAntes xml;

                declare @XMLDespues xml;

                Select @Numero_de_Cuenta = NewBen.Numero_Cuenta, @idTipoEvento = TE.id                      --Determina NumeroCuenta y idTipoEvento

                from inserted NewBen, TipoEvento TE

                where TE.nombre = @TipoEvento;

                Select @idUsuario = CA.idCliente from CuentaAhorro CA where CA.Numero_Cuenta = @Numero_de_Cuenta;  --Id del usuario

                declare @TablaXml TABLE (  --Tabla variable para sacar XML del evento

                               id int NOT NULL,

                               idPersona int NOT NULL,

                              

                               idTipoID int NOT NULL,                                --De la tabla persona

                               Nombre nvarchar(100) NOT NULL,

                               Tipo_Documento_Identificacion nvarchar(100) NOT NULL,

                               Documento_Identificacion nvarchar(100) NOT NULL,

                               Telefono1 int NOT NULL,

                               Telefono2 int NOT NULL,

                               Email nvarchar(100) NOT NULL,

                               Fecha_Nacimiento date NOT NULL,

                               idParentesco int NOT NULL,

                               Parentesco nvarchar(3) NOT NULL,

                               Porcentaje int NOT NULL,

                               Activo int NOT NULL,

                               Fecha_Desactivacion date NULL,

                               Numero_Cuenta nvarchar(100) NOT NULL

                )

                insert into @TablaXml   --Insercion del Evento en tabla variable

                select newBen.id, newBen.idPersona, P.idTipoID, P.Nombre, P.Tipo_Documento_Identificacion,

                                               P.Documento_Identificacion, P.Telefono1, P.Telefono2, P.Email, P.Fecha_Nacimiento,

                                               newBen.idParentesco, newBen.Parentezco, newBen.Porcentaje, newBen.Activo,

                                               newBen.Fecha_Desactivacion, newBen.Numero_Cuenta

                from inserted newBen, Persona P

                Where P.id = newBen,idPersona

                set @XMLDespues = (Select * from @TablaXml as TablaXml for xml Auto, ELEMENTS XSINIL);  --XML del Evento

                insert into Evento (idTipoEvento, idUser, _IP, Fecha, XMLAntes, XMLDespues)  --Insercion final al evento

                values(@idTipoEvento, @idUsuario, @IP, @Fecha, @XMLAntes, @XMLDespues);

END

Tiempo dedicado al día de hoy: 2 horas

Procesamiento de las cuentas objetivo (depósitos)

Se saca la tarde del domingo para realizar el procesamiento de los depósitos en las cuentas objetivo.

Primero se creó la tabla variable @CuentasObjetivoPorAcreditar donde se almacenan los id de las cuentas objetivo activas cuya fecha de próximo crédito es igual a la fecha de interacción. De esta tabla se obtiene un @lo y @hi para iterar sobre ella.

Seguidamente, con estas cuentas seleccionadas se inicia una transacción donde se realizan los siguientes procesos:

1. Se debita el monto que indica la cuenta objetivo que se ahorra mensualmente a la cuenta de ahorro a la que está asociada. Pero ANTES de hacerlo se verifica si el saldo de la cuenta de ahorro queda negativo, porque si es así entonces NO se realiza el movimiento de dinero.
2. Si el saldo no queda negativo se hace un UPDATE sobre la tabla Cuenta_Ahorro para actualizar el saldo.
3. Se inserta el movimiento de CO en la tabla de movimientos de la cuenta objetivo
4. Una vez debitado el monto de la cuenta de ahorro, se acredita dicho monto a la cuenta objetivo. Para ello se verifica primero si la fecha de próximo crédito aún no ha alcanzado la fecha final de la cuenta objetivo. 
5. Si la fecha de próximo crédito alcanzó a la fecha final de la CO entonces se procesa el último movimiento, se desactiva la cuenta objetivo de manera lógica (se pone en 0 el campo "Activo")  y se transfiere todo el saldo ahorrado de la cuenta objetivo a la cuenta de ahorro.

-- Procesar depositos en cuentas objetivo

               DECLARE @CuentasObjetivoPorAcreditar TABLE
(
sec int identity(1,1),
idCuentaObjetivo int
)

               IF EXISTS(SELECT CO.id FROM CuentaObjetivo CO WHERE CO.Fecha_Proximo_Credito = @fechaIteracion and CO.Activo = 1)

               BEGIN

                       SET @lo = (SELECT COUNT(CO.sec) FROM @CuentasObjetivoPorAcreditar CO ) + 1
                       INSERT INTO @CuentasObjetivoPorAcreditar
                       SELECT CO.id
                       FROM CuentaObjetivo CO
                       WHERE CO.Fecha_Proximo_Credito = @fechaIteracion
                       SET @hi = (SELECT COUNT(CO.sec) FROM @CuentasObjetivoPorAcreditar CO )

                       BEGIN TRY
                       IF @@TRANCOUNT = 0
                               BEGIN
                                       SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
                                       BEGIN TRANSACTION ProcesarCuentasObjetivo
                                       SET @InicioTran = 1
                               END
                               WHILE @lo <= @hi
                               BEGIN

                                       -- SE DEBITA EL MONTO A AHORRAR EN LA CUENTA OBJETIVO DE LA CUENTA DE AHORRO

                                       -- Se seleccionan algunas variables que se necesitan para el proceso
                                       SELECT @Monto_Ahorro = CO.Monto_Ahorro,
                                                  @Numero_Cuenta_CO = CO.Numero_Cuenta
                                       FROM CuentaObjetivo CO
                                       INNER JOIN @CuentasObjetivoPorAcreditar C ON CO.id = C.idCuentaObjetivo and C.sec = @lo

                                       SELECT @SaldoCuenta_Debitar = CA.Saldo
                                       FROM Cuenta_Ahorro CA
                                       WHERE CA.Numero_Cuenta = @Numero_Cuenta_CO

                                       -- Se verifica primero si el saldo de la cuenta queda negativo al restar el monto de ahorro
                                       -- Si el saldo de la cuenta de ahorro no queda negativo se realiza la transaccion a la cuenta objetivo
                                       IF ((@SaldoCuenta_Debitar - @Monto_Ahorro) > 0)
                                       BEGIN
                                               UPDATE Cuenta_Ahorro
                                               SET Saldo = Saldo - @Monto_Ahorro
                                               WHERE Numero_Cuenta = @Numero_Cuenta_CO

                                               -- Se registra en la tabla de movimientos de CO la transaccion
                                               INSERT INTO MovimientoCO
                                               SELECT CO.id, TCO.id, @fechaIteracion, CO.Monto_Ahorro, CO.Descripcion
                                               FROM @CuentasObjetivoPorAcreditar C
                                               INNER JOIN CuentaObjetivo CO ON CO.id = C.idCuentaObjetivo
                                               INNER JOIN TipoMovimientoCO TCO ON TCO.Nombre = 'Depósito'
                                               WHERE C.sec = @lo

                                               -- UNA VEZ REDUCIDO EL MONTO AHORRAR DE LA CUENTA DE AHORRO,
                                               -- SE ACREDITA EL MONTO A LA CUENTA OBJETIVO CORRESPONDIENTE

                                               -- Se selecciona la fecha del ultimo credito y la fecha final

                                               SELECT @Fecha_Proximo_Credito = CO.Fecha_Proximo_Credito,
                                                          @Fecha_Final_CO = CO.Fecha_Final
                                               FROM CuentaObjetivo CO
                                               WHERE CO.id = (SELECT C.idCuentaObjetivo FROM @CuentasObjetivoPorAcreditar C WHERE C.sec = @lo)

                                               IF (@Fecha_Proximo_Credito < @Fecha_Final_CO)
                                               BEGIN

                                                       UPDATE CuentaObjetivo
                                                       SET Saldo = (Saldo + Monto_Ahorro),
                                                               Fecha_Proximo_Credito = DATEADD(MONTH,1,Fecha_Proximo_Credito)
                                                       WHERE id = (SELECT C.idCuentaObjetivo FROM @CuentasObjetivoPorAcreditar C WHERE C.sec = @lo)
                                               END
                                               ELSE
                                               BEGIN
                                                       UPDATE CuentaObjetivo
                                                       SET Saldo = (Saldo + Monto_Ahorro)
                                                       WHERE id = (SELECT C.idCuentaObjetivo FROM @CuentasObjetivoPorAcreditar C WHERE C.sec = @lo)
                                                       -- Si entra aqui significa que llego a la fecha final de la cuenta objetivo
                                                       -- por lo que se procede a procesar la redencion en cuentas objetivo
                                                       IF (@Fecha_Proximo_Credito = @Fecha_Final_CO)
                                                       BEGIN      
                                                               -- Se acredita la cuenta de ahorro
                                                               UPDATE Cuenta_Ahorro
                                                               SET Saldo = Saldo + (SELECT CO.Saldo
                                                                                        FROM CuentaObjetivo CO
                                                                                        INNER JOIN @CuentasObjetivoPorAcreditar C ON CO.id = C.idCuentaObjetivo and C.sec = @lo)
                                                               WHERE id = (SELECT CO.idCuenta
                                                                                       FROM CuentaObjetivo CO
                                                                                       INNER JOIN @CuentasObjetivoPorAcreditar C ON CO.id = C.idCuentaObjetivo and C.sec = @lo)

                                                               -- Se debita la cuenta objetivo y se desactiva

                                                               UPDATE CuentaObjetivo
                                                               SET Saldo = 0,
                                                                       Activo = 0
                                                               WHERE id = (SELECT C.idCuentaObjetivo FROM @CuentasObjetivoPorAcreditar C WHERE C.sec = @lo)
                                                       END
                                               END
                                       END
                                       -- Si al hacer la transaccion el saldo quedaba negativo en la cuenta de ahorro
                                       ELSE
                                       BEGIN

                                               INSERT INTO @CuentasObjetivo_CasoDeSaldoNegativo
                                               SELECT CO.Numero_Cuenta, CO.Fecha_Inicio, CO.Fecha_Final, CO.Fecha_Proximo_Credito
                                               FROM CuentaObjetivo CO
                                               WHERE CO.id = (SELECT C.idCuentaObjetivo FROM @CuentasObjetivoPorAcreditar C WHERE C.sec = @lo)
                                       END

                                       SET @lo = @lo + 1

                               END

                               IF (@InicioTran = 1)

                                       BEGIN
                                               COMMIT TRANSACTION ProcesarCuentasObjetivo
                                               SET @InicioTran = 0
                                       END
                       END TRY
                       BEGIN CATCH
                               IF @InicioTran = 1
                               BEGIN
                                       ROLLBACK TRANSACTION ProcesarCuentasObjetivo
                                       SELECT 'Hubo un error al procesar las cuentas objetivo'
                                       Return -100007
                               END
                       END CATCH

               END

Horas trabajadas: 3:30 h