Database Triggers and Scheduled Events in CashMan H.A.

Triggers in CashMan H.A. automate state transitions and cross-table updates without requiring any application-level logic in PHP. Whenever a row is inserted, updated, or deleted in a key table, the database itself enforces the downstream consequences — balance recalculations, flag updates, audit entries, and notification delivery all happen inside the same transaction. Scheduled events complement triggers by handling time-based operations that cannot be tied to a user action: detecting newly overdue installments, applying late penalties, expiring security codes, and closing idle support tickets. All trigger and event definitions live in:

ScriptSQL/Triggers/tg_cashmanha.sql — 21 triggers
ScriptSQL/Events/ev_cashmanha.sql — 5 events

Triggers

Overview Table

Trigger Name	Table	Event	Action
`HabilitarSistemaCuentasClientes_PortalCashman`	`cuentas`	AFTER INSERT	Sets `poseecuenta='si'` on `usuarios` for the new account’s owner
`CambioEstadoComprobadorCuotasMensualesClientes`	`cuotas`	AFTER INSERT	Sets `cuotas_generadas='si'` on `creditos` once any installment is inserted
`HabilitarSistemaPortalClientes_Creditos`	`cuotas`	AFTER INSERT	Sets `habilitarsistema='si'` on `usuarios`, unlocking the client portal
`ComprobacionCompletarPerfilUsuarios`	`detalleusuarios`	AFTER INSERT	Sets `completoperfil='si'` on `usuarios` once the KYC profile row is created
`HabilitarNuevasSolicitudesCrediticias_Clientes`	`historicocreditos`	AFTER INSERT	Sets `habilitarnuevoscreditos='si'` on `usuarios` when a credit is archived to history
`ComprobacionSolicitudCrediticiaCanceladaClientes_EnvioHistorico`	`historicocuotascreditos`	AFTER INSERT	Sets `enviaralhistorico='si'` on the parent credit record
`OcultarTransaccionesProcesadasPortalClientes_CreditosCancelados`	`historicocuotascreditos`	AFTER INSERT	Sets `ocultartransacciones_clientes='si'` on the parent credit, hiding old payment rows from the client portal
`EnvioNotificacionNuevosMensajesUsuarios`	`mensajeria`	AFTER INSERT	Inserts a `nuevomensaje` notification row in `notificaciones` for the recipient
`CambioEstadoCancelacionCreditosClientes_UltimaCuotaPagada`	`transacciones`	AFTER INSERT	Sets credit `estado='cancelado'` when `saldocredito` drops below 1 or to 0
`CambioEstadoCrediticio_EstadoExcelenteCreditosClientes`	`transacciones`	AFTER INSERT	Promotes credit rating to `Excelente` if ≥ 10 on-time payments and current rating is `Nuevo Cliente`
`CambioEstadoCuotasVencidas`	`transacciones`	AFTER INSERT	Changes overdue installment flag from `SI` to `PT` (paid-late) when a payment is received
`CambioEstadoCuotas_OrdenPagoCreditosClientes`	`transacciones`	AFTER INSERT	Sets the paid installment’s `estadocuota='cancelado'`
`CambioEstadoRecordCrediticio_CreditocClientes`	`transacciones`	AFTER INSERT	Sets credit rating to `Regular` after 2 late payments; `Deficiente` after more than 5
`EnvioNotificacionPagoRecibidoClientesCashmanHa`	`transacciones`	AFTER INSERT	Inserts a `pagorecibido` notification for the paying client
`RecalcularSaldoFinal_CreditosClientes`	`transacciones`	AFTER INSERT	Subtracts the capital portion from `creditos.saldocredito` (mortgage: annual term ÷ 12; other products: monthly term)
`RegistroTransaccionesCuotasCreditosClientes_Historico`	`transacciones`	AFTER INSERT	Mirrors every payment row into `historicotransacciones` for immutable audit
`AnularTransaccionesCuentasClientes`	`transaccionescuentasclientes`	AFTER UPDATE	On `AnularRetiro` adds amount back to account balance; on `AnularDeposito` subtracts it
`RecalcularSaldoFinal_CuentasAhorroClientes`	`transaccionescuentasclientes`	AFTER INSERT	Adds amount to `cuentas.montocuenta` for `Entrada`; subtracts for `Salida`
`RecalcularSaldoFinal_TransferenciasClientes`	`transferencias`	AFTER INSERT	Debits source account and credits destination account atomically
`RegistroMovimientosTransferencias_EnvioTransferencias`	`transferencias`	AFTER INSERT	Inserts two rows in `transaccionescuentasclientes`: one `EnvioTransferencia` for sender, one `DepositoTransferencia` for recipient
`EnviarSolicitudesCreditosDenegadas_HistoricoCreditos`	`creditos`	AFTER DELETE	Archives the deleted credit’s key fields into `historicocreditos`

Trigger Details

transacciones AFTER INSERT chain (8 triggers)

All eight triggers below fire on the same event — AFTER INSERT ON transacciones. MySQL fires them in definition order. A single installment payment therefore atomically:

Marks the installment paid (CambioEstadoCuotas_OrdenPagoCreditosClientes)
Flips the overdue flag to paid-late if applicable (CambioEstadoCuotasVencidas)
Recalculates the running credit balance (RecalcularSaldoFinal_CreditosClientes)
Checks if the balance has hit zero and marks the credit cancelled (CambioEstadoCancelacionCreditosClientes_UltimaCuotaPagada)
Updates the client credit rating for on-time payment track record (CambioEstadoCrediticio_EstadoExcelenteCreditosClientes)
Updates the client credit rating for late payment track record (CambioEstadoRecordCrediticio_CreditocClientes)
Sends a payment confirmation notification (EnvioNotificacionPagoRecibidoClientesCashmanHa)
Mirrors the row to the audit history table (RegistroTransaccionesCuotasCreditosClientes_Historico)

RecalcularSaldoFinal_CreditosClientes — balance formula

The trigger computes the capital portion to deduct from saldocredito depending on the product type:

-- Mortgage products (idproducto = 3): term is in years, convert to months
IF _idproducto = 3 THEN
    SET calculocapital = _montocredito / (_plazocredito * 12);
-- All other products: term already in months
ELSE
    SET calculocapital = _montocredito / _plazocredito;
END IF;

UPDATE creditos
    SET saldocredito = saldocredito - calculocapital
    WHERE idcreditos = NEW.idcreditos;

RecalcularSaldoFinal_TransferenciasClientes — atomic balance swap

When a row is inserted into transferencias, this trigger performs two sequential UPDATEs inside the same trigger body, ensuring the debit and credit happen atomically:

-- Debit the sender's account
UPDATE cuentas
    SET montocuenta = montocuenta - _monto
    WHERE idcuentas = NEW.idcuentas;

-- Credit the recipient's account
UPDATE cuentas
    SET montocuenta = montocuenta + _monto
    WHERE idcuentas = NEW.idcuentadestino;

The companion trigger RegistroMovimientosTransferencias_EnvioTransferencias then writes two ledger rows into transaccionescuentasclientes for both parties.

Credit rating state machine

Three triggers on transacciones collaborate to maintain creditos.estadocrediticio across the credit lifetime:

Initial state: "Nuevo Cliente"
    ↓ ≥ 10 on-time payments
"Excelente"
    ↓ 2+ late payments
"Regular"
    ↓ more than 5 late payments
"Deficiente"

The views vista_contadorpagosatiempo_creditosclientes and vista_contadorpagoscuotastardias_creditosclientes power the counts queried inside the trigger bodies.

Known Issue

Precision mismatch in RecalcularSaldoFinal_CreditosClientesThe local variable _montocredito inside this trigger is declared as decimal(9,2):

DECLARE _montocredito decimal(9,2);

The corresponding column creditos.montocredito is also decimal(9,2), so this is consistent. However, the intermediate calculation variable calculocapital is declared as decimal(15,6) — which is correct for precision. If you are importing the schema and a credit amount causes intermediate values to exceed decimal(9,2) range (values above 9,999,999.99), the local variable assignment silently truncates.For very large mortgage principals, change the declaration to:

DECLARE _montocredito decimal(15,6);

before importing tg_cashmanha.sql into production.

Scheduled Events

The 5 events defined in ScriptSQL/Events/ev_cashmanha.sql run automatically as long as the MySQL event scheduler is enabled. All five were created with STARTS '2022-04-08 00:00:00' as the anchor date.

The MySQL event scheduler is disabled by default in most installations. Before importing the events file, enable it with:

SET GLOBAL event_scheduler = ON;

To persist across restarts, add event_scheduler = ON to my.cnf / my.ini under the [mysqld] section.

Events Reference

Event Name	Schedule	Preservation	Action
`CambioEstadosCodigoSeguridad`	Every 30 seconds	`ON COMPLETION PRESERVE`	Calls `CALL CambioEstadoCodigoSeguridad()` — marks expired password-recovery tokens as `vencido`
`CambioEstadoCuotasClientes_IncumplimientoPagos`	Every 100 seconds	`ON COMPLETION NOT PRESERVE`	Sets `incumplimiento_pago='SI'` on `cuotas` rows via `vista_calculodiasfechavencimiento_cuotasclientes` where `dias_incumplimiento > 0` and installment is still `pendiente`
`SumatoriaMoraCuotasClientesVencidas`	Every 1 day	`ON COMPLETION NOT PRESERVE`	Adds `$5.99` penalty to `montocancelar` on every overdue installment (`incumplimiento_pago='SI'`)
`CambioEstadoTicketsReportesPlataforma_Inactividad`	Every 2 minutes	`ON COMPLETION NOT PRESERVE`	Auto-closes support tickets via `vista_calculo_ultimaactividad_ticketsreportesplataforma` where status is `resuelto`, `no resuelto`, or idle for more than 3 days
`ExpirarCodigoSeguridad_TransferenciasClientes`	Every 30 seconds	`ON COMPLETION NOT PRESERVE`	Sets transfer OTP codes to `Vencido` via `vista_calculaduracioncodigoseguridad_transferencias` where `minutos_expiracion > 2`

Event Behaviour Notes

CambioEstadosCodigoSeguridad vs ExpirarCodigoSeguridad_TransferenciasClientes

These two events look similar but target different tables and different security flows:

CambioEstadosCodigoSeguridad — Targets password recovery codes in the recuperacion table (via the vista_calculoexpiracion_codigocambiocredencialesusuarios view). It calls the stored procedure CambioEstadoCodigoSeguridad(). Codes expire after 6 minutes (minutos_expiracion > 6) and must already be in the usado state. This event is PRESERVEd — it continues to exist after it runs.
ExpirarCodigoSeguridad_TransferenciasClientes — Targets transfer OTP codes in the codigostransferencias table (via vista_calculaduracioncodigoseguridad_transferencias). OTPs expire after 2 minutes (minutos_expiracion > 2), setting estado='Vencido'. This is a direct UPDATE, not a procedure call.

Daily late penalty accumulation

SumatoriaMoraCuotasClientesVencidas runs once per day and adds a flat $5.99 fee to every installment where incumplimiento_pago = 'SI'. This means a client overdue by 10 days has accumulated $59.90 in penalties on top of the original montocuota.The stored procedure SumatoriaIncumplimientoMora_CuotasClientes performs the same calculation on demand and can be called manually to force a penalty recalculation outside the scheduled cycle.

ON COMPLETION NOT PRESERVE

Four of the five events are defined with ON COMPLETION NOT PRESERVE. In MySQL this means the event definition is automatically deleted from the mysql.event table after it would have naturally concluded. Because all four use ON SCHEDULE EVERY ... (recurring), they never naturally conclude and so are never dropped — the NOT PRESERVE flag only matters for one-time events. The net effect is the same as PRESERVE for recurring schedules.

Trigger & Event Dependency Map

INSERT cuotas
  └─► CambioEstadoComprobadorCuotasMensualesClientes  →  creditos.cuotas_generadas = 'si'
  └─► HabilitarSistemaPortalClientes_Creditos          →  usuarios.habilitarsistema = 'si'

INSERT detalleusuarios
  └─► ComprobacionCompletarPerfilUsuarios              →  usuarios.completoperfil = 'si'

INSERT cuentas
  └─► HabilitarSistemaCuentasClientes_PortalCashman    →  usuarios.poseecuenta = 'si'

INSERT mensajeria
  └─► EnvioNotificacionNuevosMensajesUsuarios          →  notificaciones (INSERT)

INSERT transacciones  (8 triggers)
  ├─► CambioEstadoCuotas_OrdenPagoCreditosClientes     →  cuotas.estadocuota = 'cancelado'
  ├─► CambioEstadoCuotasVencidas                       →  cuotas.incumplimiento_pago = 'PT'
  ├─► RecalcularSaldoFinal_CreditosClientes            →  creditos.saldocredito -= capital
  ├─► CambioEstadoCancelacionCreditosClientes_*        →  creditos.estado = 'cancelado' if balance ≤ 0
  ├─► CambioEstadoCrediticio_Excelente                 →  creditos.estadocrediticio = 'Excelente'
  ├─► CambioEstadoRecordCrediticio_*                   →  creditos.estadocrediticio = 'Regular'/'Deficiente'
  ├─► EnvioNotificacionPagoRecibido*                   →  notificaciones (INSERT)
  └─► RegistroTransacciones*_Historico                 →  historicotransacciones (INSERT)

INSERT transaccionescuentasclientes
  └─► RecalcularSaldoFinal_CuentasAhorroClientes       →  cuentas.montocuenta ± monto

UPDATE transaccionescuentasclientes
  └─► AnularTransaccionesCuentasClientes               →  cuentas.montocuenta ± monto (reversal)

INSERT transferencias  (2 triggers)
  ├─► RecalcularSaldoFinal_TransferenciasClientes      →  cuentas balance swap
  └─► RegistroMovimientosTransferencias_*              →  transaccionescuentasclientes (2 INSERTs)

INSERT historicocreditos
  └─► HabilitarNuevasSolicitudesCrediticias_Clientes   →  usuarios.habilitarnuevoscreditos = 'si'

INSERT historicocuotascreditos  (2 triggers)
  ├─► ComprobacionSolicitudCrediticia*_EnvioHistorico  →  creditos.enviaralhistorico = 'si'
  └─► OcultarTransacciones*_CreditosCancelados         →  creditos.ocultartransacciones_clientes = 'si'

DELETE creditos
  └─► EnviarSolicitudesCreditosDenegadas_*             →  historicocreditos (INSERT)

Database

Controllers & Routing

Credit Products

Database Triggers and Scheduled Events in CashMan H.A.

Triggers

Overview Table

Trigger Details

Known Issue

Scheduled Events

Events Reference

Event Behaviour Notes

Trigger & Event Dependency Map

Build docs developers (and LLMs) love

Database

Controllers & Routing

Credit Products

Documentation Index

​Triggers

​Overview Table

​Trigger Details

​Known Issue

​Scheduled Events

​Events Reference

​Event Behaviour Notes

​Trigger & Event Dependency Map

Build docs developers (and LLMs) love

Triggers

Overview Table

Trigger Details

Known Issue

Scheduled Events

Events Reference

Event Behaviour Notes

Trigger & Event Dependency Map