MariaDB [fechas]> select * from pagos where year(fechaadquisicion) < 2012 or (year(fechaadquisicion) = 2012 and month(fechaadquisicion) <6); +--------+-------------------+------------------+ | Identi | descripcion | fechaadquisicion | +--------+-------------------+------------------+ | 0004 | ram | 2012-03-27 | | 0006 | ups | 2012-03-25 | | 0007 | regulador voltaje | 2012-05-08 | | 0008 | cableado | 2012-04-24 | | 0010 | Escanner | 2012-03-21 | | 0011 | lapiz optico | 2012-02-12 | | 0013 | Procesador SN | 2012-04-14 | | 0015 | hub | 2012-01-16 | +--------+-------------------+------------------+ 8 rows in set (0.014 sec) MariaDB [fechas]> select * from pagos where fechaadquisicion = (select min(fechaadquisicion) from pagos); +--------+-------------+------------------+ | Identi | descripcion | fechaadquisicion | +--------+-------------+------------------+ | 0015 | hub | 2012-01-16 | +--------+-------------+------------------+ 1 row in set (0.019 sec) MariaDB [fechas]> select * from pagos where descripcion like '%o' or descripcion like '%m'; +--------+--------------------+------------------+ | Identi | descripcion | fechaadquisicion | +--------+--------------------+------------------+ | 0003 | teclado | 2012-06-04 | | 0004 | ram | 2012-03-27 | | 0008 | cableado | 2012-04-24 | | 0011 | lapiz optico | 2012-02-12 | | 0012 | Procedador pentium | 2012-06-19 | | 0021 | disco duro | 2013-11-22 | +--------+--------------------+------------------+ 6 rows in set (0.008 sec) MariaDB [fechas]> select * from pagos where descripcion like '%a' or descripcion like '%e' or descripcion like '%i' or descripcion like '%o' or descripcion like '%u'; +--------+-------------------+------------------+ | Identi | descripcion | fechaadquisicion | +--------+-------------------+------------------+ | 0002 | mouse | 2013-07-29 | | 0003 | teclado | 2012-06-04 | | 0007 | regulador voltaje | 2012-05-08 | | 0008 | cableado | 2012-04-24 | | 0011 | lapiz optico | 2012-02-12 | | 0017 | Camara | 2013-03-18 | | 0020 | Fuente | 2013-07-11 | | 0021 | disco duro | 2013-11-22 | +--------+-------------------+------------------+ 8 rows in set (0.004 sec) MariaDB [fechas]> select descripcion, fechaadquisicion, Identi, -> case -> when month(fechaadquisicion) between 1 and 6 then date_add(fechaadquisicion, interval 5 month) -> else fechaadquisicion -> end as limite_pago -> from pagos; +--------------------+------------------+--------+-------------+ | descripcion | fechaadquisicion | Identi | limite_pago | +--------------------+------------------+--------+-------------+ | Chasis | 2012-09-02 | 0001 | 2012-09-02 | | mouse | 2013-07-29 | 0002 | 2013-07-29 | | teclado | 2012-06-04 | 0003 | 2012-11-04 | | ram | 2012-03-27 | 0004 | 2012-08-27 | | Memoria usb | 2012-12-06 | 0005 | 2012-12-06 | | ups | 2012-03-25 | 0006 | 2012-08-25 | | regulador voltaje | 2012-05-08 | 0007 | 2012-10-08 | | cableado | 2012-04-24 | 0008 | 2012-09-24 | | Tablet | 2012-08-10 | 0009 | 2012-08-10 | | Escanner | 2012-03-21 | 0010 | 2012-08-21 | | lapiz optico | 2012-02-12 | 0011 | 2012-07-12 | | Procedador pentium | 2012-06-19 | 0012 | 2012-11-19 | | Procesador SN | 2012-04-14 | 0013 | 2012-09-14 | | Unidad CD | 2012-09-17 | 0014 | 2012-09-17 | | hub | 2012-01-16 | 0015 | 2012-06-16 | | Multifuncional | 2013-04-15 | 0016 | 2013-09-15 | | Camara | 2013-03-18 | 0017 | 2013-08-18 | | Board | 2013-01-13 | 0018 | 2013-06-13 | | impresora laser | 2013-10-20 | 0019 | 2013-10-20 | | Fuente | 2013-07-11 | 0020 | 2013-07-11 | | disco duro | 2013-11-22 | 0021 | 2013-11-22 | +--------------------+------------------+--------+-------------+ 21 rows in set (0.001 sec) MariaDB [fechas]> select *, -> case -> when month(fechaadquisicion) between 7 and 12 then date_add(fechaadquisicion, interval 8 month) -> else fechaadquisicion -> end as limite_pago -> from pagos; +--------+--------------------+------------------+-------------+ | Identi | descripcion | fechaadquisicion | limite_pago | +--------+--------------------+------------------+-------------+ | 0001 | Chasis | 2012-09-02 | 2013-05-02 | | 0002 | mouse | 2013-07-29 | 2014-03-29 | | 0003 | teclado | 2012-06-04 | 2012-06-04 | | 0004 | ram | 2012-03-27 | 2012-03-27 | | 0005 | Memoria usb | 2012-12-06 | 2013-08-06 | | 0006 | ups | 2012-03-25 | 2012-03-25 | | 0007 | regulador voltaje | 2012-05-08 | 2012-05-08 | | 0008 | cableado | 2012-04-24 | 2012-04-24 | | 0009 | Tablet | 2012-08-10 | 2013-04-10 | | 0010 | Escanner | 2012-03-21 | 2012-03-21 | | 0011 | lapiz optico | 2012-02-12 | 2012-02-12 | | 0012 | Procedador pentium | 2012-06-19 | 2012-06-19 | | 0013 | Procesador SN | 2012-04-14 | 2012-04-14 | | 0014 | Unidad CD | 2012-09-17 | 2013-05-17 | | 0015 | hub | 2012-01-16 | 2012-01-16 | | 0016 | Multifuncional | 2013-04-15 | 2013-04-15 | | 0017 | Camara | 2013-03-18 | 2013-03-18 | | 0018 | Board | 2013-01-13 | 2013-01-13 | | 0019 | impresora laser | 2013-10-20 | 2014-06-20 | | 0020 | Fuente | 2013-07-11 | 2014-03-11 | | 0021 | disco duro | 2013-11-22 | 2014-07-22 | +--------+--------------------+------------------+-------------+ 21 rows in set (0.002 sec) MariaDB [fechas]> auditoriaas y triggersAUDITORIAS Y TRIGGERS MariaDB [fechas]> create table pagos_insertados -> (id int auto_increment not null primary key, -> usuario varchar(50) not null, -> fecha datetime not null, -> id_pago varchar(4) not null, -> descripcion varchar(50), -> fechaadquisicion date); Query OK, 0 rows affected (0.114 sec) MariaDB [fechas]> describe pagos_insertados; +------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | usuario | varchar(50) | NO | | NULL | | | fecha | datetime | NO | | NULL | | | id_pago | varchar(4) | NO | | NULL | | | descripcion | varchar(50) | YES | | NULL | | | fechaadquisicion | date | YES | | NULL | | +------------------+-------------+------+-----+---------+----------------+ 6 rows in set (0.052 sec) MariaDB [fechas]> create table pagos_modificados -> (id int auto_increment not null primary key, -> usuario varchar(50) not null, -> fecha datetime not null, -> id_pago varchar(4) not null, -> ant_descripcion varchar(50) not null, -> nueva_descripcion varchar(50) not null, -> fechaadquisicion_ant date not null, -> fechaadquisicion_nueva date not null); Query OK, 0 rows affected (0.016 sec) MariaDB [fechas]> describe pagos_modificados; +------------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | usuario | varchar(50) | NO | | NULL | | | fecha | datetime | NO | | NULL | | | id_pago | varchar(4) | NO | | NULL | | | ant_descripcion | varchar(50) | NO | | NULL | | | nueva_descripcion | varchar(50) | NO | | NULL | | | fechaadquisicion_ant | date | NO | | NULL | | | fechaadquisicion_nueva | date | NO | | NULL | | +------------------------+-------------+------+-----+---------+----------------+ 8 rows in set (0.029 sec) MariaDB [fechas]> create table pagos_eliminados -> (id int auto_increment not null primary key, -> usuario varchar(50) not null, -> fecha datetime not null, -> id_pago varchar(4) not null, -> descripcion varchar(50) not null, -> fechaadquisicion date not null); Query OK, 0 rows affected (0.020 sec) MariaDB [fechas]> describe pagos_eliminados; +------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | usuario | varchar(50) | NO | | NULL | | | fecha | datetime | NO | | NULL | | | id_pago | varchar(4) | NO | | NULL | | | descripcion | varchar(50) | NO | | NULL | | | fechaadquisicion | date | NO | | NULL | | +------------------+-------------+------+-----+---------+----------------+ 6 rows in set (0.024 sec) MariaDB [fechas]> -- CREACION DE TRIGGER PARA INSERTAR PAGOS MariaDB [fechas]> delimiter // MariaDB [fechas]> create trigger insertar_pagos after insert on pagos -> for each row -> begin -> insert into pagos_insertados(usuario,fecha,id_pago,descripcion,fechaadquisicion) values (CURRENT_USER(), NOW(), NEW.Identi, NEW.descripcion, NEW.fechaadquisicion); -> end -> // Query OK, 0 rows affected (0.018 sec) MariaDB [fechas]> delimiter ; MariaDB [fechas]> -- CREACION DE TRIGGER PARA MODIFICAR PAGOS MariaDB [fechas]> delimiter // MariaDB [fechas]> create trigger modificar_pagos after update on pagos -> for each row -> begin -> insert into pagos_modificados (usuario,fecha, id_pago, ant_descripcion, nueva_descripcion, fechaadquisicion_ant, fechaadquisicion_nueva) values (current_user(),now(), old.Identi, old.descripcion, new.descripcion, old.fechaadquisicion, new.fechaadquisicion); -> end -> // Query OK, 0 rows affected (0.014 sec) MariaDB [fechas]> delimiter ; MariaDB [fechas]> -- CREACION DE TRIGGER PARA ELIMINAR PAGOS MariaDB [fechas]> delimiter // MariaDB [fechas]> create trigger eliminar_pagos after delete on pagos -> for each row -> begin -> insert into pagos_eliminados (usuario,fecha,id_pago,descripcion,fechaadquisicion) values(current_user(), now(), old.Identi, old.descripcion, old.fechaadquisicion); -> end -> // Query OK, 0 rows affected (0.009 sec) MariaDB [fechas]> delimiter ; MariaDB [fechas]> show triggers; +-----------------+--------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +-----------------+--------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | insertar_pagos | INSERT | pagos | begin insert into pagos_insertados (usuario, id_pago, descripcion, fechaadquisicion) values (current_user(), now(), new.Identi, new.descripcion, new.fechaadquisicion); end | AFTER | 2025-03-16 12:20:34.89 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | modificar_pagos | UPDATE | pagos | begin insert into pagos_modificados (usuario,fecha, id_pago, ant_descripcion, nueva_descripcion, fechaadquisicion_ant, fechaadquisicion_nueva) values (current_user(),now(), old.Identi, old.descripcion, new.descripcion, old.fechaadquisicion, new.fechaadquisicion); end | AFTER | 2025-03-16 12:23:53.54 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | eliminar_pagos | DELETE | pagos | begin insert into pagos_eliminados (usuario,fecha,id_pago,descripcion,fechaadquisicion) values(current_user(), now(), old.Identi, old.descripcion, old.fechaadquisicion); end | AFTER | 2025-03-16 12:26:46.75 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +-----------------+--------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.023 sec) MariaDB [fechas]> -- PROBANDO TRIGGER PARA INSERTAR insert into pagos (Identi, descripcion, fechaadquisicion) values ('0022', 'Tarjeta de sonido', '2023-05-20'); Query OK, 1 row affected (0.011 sec) MariaDB [fechas]> select * from pagos; +--------+--------------------+------------------+ | Identi | descripcion | fechaadquisicion | +--------+--------------------+------------------+ | 0001 | Chasis | 2012-09-02 | | 0002 | mouse | 2013-07-29 | | 0003 | teclado | 2012-06-04 | | 0004 | ram | 2012-03-27 | | 0005 | Memoria usb | 2012-12-06 | | 0006 | ups | 2012-03-25 | | 0007 | regulador voltaje | 2012-05-08 | | 0008 | cableado | 2012-04-24 | | 0009 | Tablet | 2012-08-10 | | 0010 | Escanner | 2012-03-21 | | 0011 | lapiz optico | 2012-02-12 | | 0012 | Procedador pentium | 2012-06-19 | | 0013 | Procesador SN | 2012-04-14 | | 0014 | Unidad CD | 2012-09-17 | | 0015 | hub | 2012-01-16 | | 0016 | Multifuncional | 2013-04-15 | | 0017 | Camara | 2013-03-18 | | 0018 | Board | 2013-01-13 | | 0019 | impresora laser | 2013-10-20 | | 0020 | Fuente | 2013-07-11 | | 0021 | disco duro | 2013-11-22 | | 0022 | Tarjeta de sonido | 2023-05-20 | +--------+--------------------+------------------+ 22 rows in set (0.001 sec) MariaDB [fechas]> select * from pagos_insertados; +----+----------------+---------------------+---------+-------------------+------------------+ | id | usuario | fecha | id_pago | descripcion | fechaadquisicion | +----+----------------+---------------------+---------+-------------------+------------------+ | 1 | root@localhost | 2025-03-16 12:40:35 | 0022 | Tarjeta de sonido | 2023-05-20 | +----+----------------+---------------------+---------+-------------------+------------------+ 1 row in set (0.001 sec) MariaDB [fechas]> -- PROBANDO TRIGGER PARA MODIFICAR MariaDB [fechas]> update pagos set descripcion = 'cableado UTP', fechaadquisicion ='2015-10-01' where Identi ='0008'; Query OK, 1 row affected (0.030 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [fechas]> select * from pagos; +--------+--------------------+------------------+ | Identi | descripcion | fechaadquisicion | +--------+--------------------+------------------+ | 0001 | Chasis | 2012-09-02 | | 0002 | mouse | 2013-07-29 | | 0003 | teclado | 2012-06-04 | | 0004 | ram | 2012-03-27 | | 0005 | Memoria usb | 2012-12-06 | | 0006 | ups | 2012-03-25 | | 0007 | regulador voltaje | 2012-05-08 | | 0008 | cableado UTP | 2015-10-01 | | 0009 | Tablet | 2012-08-10 | | 0010 | Escanner | 2012-03-21 | | 0011 | lapiz optico | 2012-02-12 | | 0012 | Procedador pentium | 2012-06-19 | | 0013 | Procesador SN | 2012-04-14 | | 0014 | Unidad CD | 2012-09-17 | | 0015 | hub | 2012-01-16 | | 0016 | Multifuncional | 2013-04-15 | | 0017 | Camara | 2013-03-18 | | 0018 | Board | 2013-01-13 | | 0019 | impresora laser | 2013-10-20 | | 0020 | Fuente | 2013-07-11 | | 0021 | disco duro | 2013-11-22 | | 0022 | Tarjeta de sonido | 2023-05-20 | +--------+--------------------+------------------+ 22 rows in set (0.001 sec) MariaDB [fechas]> select * from pagos_modificados; +----+----------------+---------------------+---------+-----------------+-------------------+----------------------+------------------------+ | id | usuario | fecha | id_pago | ant_descripcion | nueva_descripcion | fechaadquisicion_ant | fechaadquisicion_nueva | +----+----------------+---------------------+---------+-----------------+-------------------+----------------------+------------------------+ | 1 | root@localhost | 2025-03-16 12:47:48 | 0008 | cableado | cableado UTP | 2012-04-24 | 2015-10-01 | +----+----------------+---------------------+---------+-----------------+-------------------+----------------------+------------------------+ 1 row in set (0.000 sec) MariaDB [fechas]> -- PROBANDO TRIGGER PARA ELIMINAR MariaDB [fechas]> delete from pagos where Identi ='0021'; Query OK, 1 row affected (0.013 sec) MariaDB [fechas]> select * from pagos; +--------+--------------------+------------------+ | Identi | descripcion | fechaadquisicion | +--------+--------------------+------------------+ | 0001 | Chasis | 2012-09-02 | | 0002 | mouse | 2013-07-29 | | 0003 | teclado | 2012-06-04 | | 0004 | ram | 2012-03-27 | | 0005 | Memoria usb | 2012-12-06 | | 0006 | ups | 2012-03-25 | | 0007 | regulador voltaje | 2012-05-08 | | 0008 | cableado UTP | 2015-10-01 | | 0009 | Tablet | 2012-08-10 | | 0010 | Escanner | 2012-03-21 | | 0011 | lapiz optico | 2012-02-12 | | 0012 | Procedador pentium | 2012-06-19 | | 0013 | Procesador SN | 2012-04-14 | | 0014 | Unidad CD | 2012-09-17 | | 0015 | hub | 2012-01-16 | | 0016 | Multifuncional | 2013-04-15 | | 0017 | Camara | 2013-03-18 | | 0018 | Board | 2013-01-13 | | 0019 | impresora laser | 2013-10-20 | | 0020 | Fuente | 2013-07-11 | | 0022 | Tarjeta de sonido | 2023-05-20 | +--------+--------------------+------------------+ 21 rows in set (0.001 sec) MariaDB [fechas]> select * from pagos_eliminados; +----+----------------+---------------------+---------+-------------+------------------+ | id | usuario | fecha | id_pago | descripcion | fechaadquisicion | +----+----------------+---------------------+---------+-------------+------------------+ | 1 | root@localhost | 2025-03-16 12:50:06 | 0021 | disco duro | 2013-11-22 | +----+----------------+---------------------+---------+-------------+------------------+ 1 row in set (0.001 sec) MariaDB [fechas]> exit