MariaDB [(none)]> create database matriculauni; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> use matriculauni; Database changed MariaDB [matriculauni]> create table estudiante -> (idestu varchar(4) not null primary key, -> nomestu varchar(80) not null, -> fechanaci date not null, -> programa varchar(60) not null, -> valmatricula int(10) not null); Query OK, 0 rows affected (0.254 sec) MariaDB [matriculauni]> describe estudiante; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | idestu | varchar(4) | NO | PRI | NULL | | | nomestu | varchar(80) | NO | | NULL | | | fechanaci | date | NO | | NULL | | | programa | varchar(60) | NO | | NULL | | | valmatricula | int(10) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 5 rows in set (0.024 sec) MariaDB [matriculauni]> create table materia -> (codmate varchar(4) not null primary key, -> nommate varchar(60) not null, -> cantcreditos int(10) not null, -> valcredito int(10) not null, -> valtotal int(10) not null); Query OK, 0 rows affected (0.227 sec) MariaDB [matriculauni]> describe materia; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | codmate | varchar(4) | NO | PRI | NULL | | | nommate | varchar(60) | NO | | NULL | | | cantcreditos | int(10) | NO | | NULL | | | valcredito | int(10) | NO | | NULL | | | valtotal | int(10) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 5 rows in set (0.026 sec) MariaDB [matriculauni]> create table matricula -> (idestu varchar(4) not null, -> codmate varchar(4) not null, -> foreign key (idestu) references estudiante(idestu) on delete cascade on update cascade, -> foreign key (codmate) references materia(codmate) on delete cascade on update cascade); Query OK, 0 rows affected (0.261 sec) MariaDB [matriculauni]> describe matricula; +---------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------+------+-----+---------+-------+ | idestu | varchar(4) | NO | MUL | NULL | | | codmate | varchar(4) | NO | MUL | NULL | | +---------+------------+------+-----+---------+-------+ 2 rows in set (0.023 sec) MariaDB [matriculauni]> insert into estudiante (idestu, nomestu, fechanaci, programa, valmatricula)values('001', 'Juan P‚rez', '2000-05-15', 'Ingenier¡a Inform tica', 0),('002', 'Mar¡a Garc¡a', '2001-10-20', 'Administraci¢n de Empresas', 0),('003', 'Carlos Mart¡nez', '1999-03-08', 'Psicolog¡a', 0),('004', 'Ana L¢pez', '2002-08-25', 'Medicina', 0),('005', 'Pedro Ram¡rez', '2000-12-10', 'Derecho', 0); Query OK, 5 rows affected (0.217 sec) Records: 5 Duplicates: 0 Warnings: 0 MariaDB [matriculauni]> select * from estudiante; +--------+-----------------+------------+----------------------------+--------------+ | idestu | nomestu | fechanaci | programa | valmatricula | +--------+-----------------+------------+----------------------------+--------------+ | 001 | Juan P‚rez | 2000-05-15 | Ingenier¡a Inform tica | 0 | | 002 | Mar¡a Garc¡a | 2001-10-20 | Administraci¢n de Empresas | 0 | | 003 | Carlos Mart¡nez | 1999-03-08 | Psicolog¡a | 0 | | 004 | Ana L¢pez | 2002-08-25 | Medicina | 0 | | 005 | Pedro Ram¡rez | 2000-12-10 | Derecho | 0 | +--------+-----------------+------------+----------------------------+--------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> delimiter // MariaDB [matriculauni]> create trigger calvalmateria before insert on materia -> for each row -> begin -> set new.valtotal=new.valcredito*new.cantcreditos; -> end -> // Query OK, 0 rows affected (0.114 sec) MariaDB [matriculauni]> delimiter ; MariaDB [matriculauni]> show triggers; +---------------+--------+---------+-------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +---------------+--------+---------+-------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +---------------+--------+---------+-------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 1 row in set (0.028 sec) MariaDB [matriculauni]> insert into materia ('001', 'Introducci¢n a la Programaci¢n', 4, 150000, 0); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''001', 'Introducci¢n a la Programaci¢n', 4, 150000, 0)' at line 1 MariaDB [matriculauni]> insert into materia values ('001', 'Introducci¢n a la Programaci¢n', 4, 150000, 0); Query OK, 1 row affected (0.117 sec) MariaDB [matriculauni]> select * from materia; +---------+--------------------------------+--------------+------------+----------+ | codmate | nommate | cantcreditos | valcredito | valtotal | +---------+--------------------------------+--------------+------------+----------+ | 001 | Introducci¢n a la Programaci¢n | 4 | 150000 | 600000 | +---------+--------------------------------+--------------+------------+----------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> insert into materia (codmate, nommate, cantcreditos, valcredito, valtotal)values('002', 'Contabilidad B sica', 3, 120000, 0),('003', 'Psicolog¡a General', 5, 140000, 0),('004', 'Anatom¡a Humana', 6, 180000, 0),('005', 'Derecho Civil', 4, 160000, 0); Query OK, 4 rows affected (0.107 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [matriculauni]> select * from materia; +---------+--------------------------------+--------------+------------+----------+ | codmate | nommate | cantcreditos | valcredito | valtotal | +---------+--------------------------------+--------------+------------+----------+ | 001 | Introducci¢n a la Programaci¢n | 4 | 150000 | 600000 | | 002 | Contabilidad B sica | 3 | 120000 | 360000 | | 003 | Psicolog¡a General | 5 | 140000 | 700000 | | 004 | Anatom¡a Humana | 6 | 180000 | 1080000 | | 005 | Derecho Civil | 4 | 160000 | 640000 | +---------+--------------------------------+--------------+------------+----------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> update estudiante set valmatricula=(select sum(materia.valtotal) from matricula inner join materia on matricula.codmateria=materia.codmateria where materia.idestu=estudiante.idestu); ERROR 1054 (42S22): Unknown column 'materia.idestu' in 'where clause' MariaDB [matriculauni]> update estudiante set valmatricula=(select sum(materia.valtotal) from matricula inner join materia on matricula.codmateria=materia.codmateria where matricula.idestu=estudiante.idestu); ERROR 1054 (42S22): Unknown column 'matricula.codmateria' in 'on clause' MariaDB [matriculauni]> update estudiante set valmatricula=(select sum(materia.valtotal) from matricula inner join materia on matricula.codmate=materia.codmate where matricula.idestu=estudiante.idestu); Query OK, 0 rows affected, 5 warnings (0.043 sec) Rows matched: 5 Changed: 0 Warnings: 5 MariaDB [matriculauni]> update estudiante set valmatricula=(select sum(materia.valtotal) from matricula inner join materia on matricula.codmate=materia.codmate where matricula.idestu=estudiante.idestu) -> : -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' :' at line 1 MariaDB [matriculauni]> select * from materia; +---------+--------------------------------+--------------+------------+----------+ | codmate | nommate | cantcreditos | valcredito | valtotal | +---------+--------------------------------+--------------+------------+----------+ | 001 | Introducci¢n a la Programaci¢n | 4 | 150000 | 600000 | | 002 | Contabilidad B sica | 3 | 120000 | 360000 | | 003 | Psicolog¡a General | 5 | 140000 | 700000 | | 004 | Anatom¡a Humana | 6 | 180000 | 1080000 | | 005 | Derecho Civil | 4 | 160000 | 640000 | +---------+--------------------------------+--------------+------------+----------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> select * from estudiante; +--------+-----------------+------------+----------------------------+--------------+ | idestu | nomestu | fechanaci | programa | valmatricula | +--------+-----------------+------------+----------------------------+--------------+ | 001 | Juan P‚rez | 2000-05-15 | Ingenier¡a Inform tica | 0 | | 002 | Mar¡a Garc¡a | 2001-10-20 | Administraci¢n de Empresas | 0 | | 003 | Carlos Mart¡nez | 1999-03-08 | Psicolog¡a | 0 | | 004 | Ana L¢pez | 2002-08-25 | Medicina | 0 | | 005 | Pedro Ram¡rez | 2000-12-10 | Derecho | 0 | +--------+-----------------+------------+----------------------------+--------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> select * from matricula; Empty set (0.000 sec) MariaDB [matriculauni]> insert into matricula values("001","001"); Query OK, 1 row affected (0.066 sec) MariaDB [matriculauni]> select * from matricula; +--------+---------+ | idestu | codmate | +--------+---------+ | 001 | 001 | +--------+---------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> update estudiante set valmatricula=(select sum(materia.valtotal) from matricula inner join materia on matricula.codmate=materia.codmate where matricula.idestu=estudiante.idestu); Query OK, 1 row affected, 4 warnings (0.066 sec) Rows matched: 5 Changed: 1 Warnings: 4 MariaDB [matriculauni]> select * from estudiante; +--------+-----------------+------------+----------------------------+--------------+ | idestu | nomestu | fechanaci | programa | valmatricula | +--------+-----------------+------------+----------------------------+--------------+ | 001 | Juan P‚rez | 2000-05-15 | Ingenier¡a Inform tica | 600000 | | 002 | Mar¡a Garc¡a | 2001-10-20 | Administraci¢n de Empresas | 0 | | 003 | Carlos Mart¡nez | 1999-03-08 | Psicolog¡a | 0 | | 004 | Ana L¢pez | 2002-08-25 | Medicina | 0 | | 005 | Pedro Ram¡rez | 2000-12-10 | Derecho | 0 | +--------+-----------------+------------+----------------------------+--------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> insert into values("001","002"); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'values("001","002")' at line 1 MariaDB [matriculauni]> insert into matricula values("001","002"); Query OK, 1 row affected (0.075 sec) MariaDB [matriculauni]> select * from matricula; +--------+---------+ | idestu | codmate | +--------+---------+ | 001 | 001 | | 001 | 002 | +--------+---------+ 2 rows in set (0.000 sec) MariaDB [matriculauni]> select * from estudiante; +--------+-----------------+------------+----------------------------+--------------+ | idestu | nomestu | fechanaci | programa | valmatricula | +--------+-----------------+------------+----------------------------+--------------+ | 001 | Juan P‚rez | 2000-05-15 | Ingenier¡a Inform tica | 600000 | | 002 | Mar¡a Garc¡a | 2001-10-20 | Administraci¢n de Empresas | 0 | | 003 | Carlos Mart¡nez | 1999-03-08 | Psicolog¡a | 0 | | 004 | Ana L¢pez | 2002-08-25 | Medicina | 0 | | 005 | Pedro Ram¡rez | 2000-12-10 | Derecho | 0 | +--------+-----------------+------------+----------------------------+--------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> update estudiante set valmatricula=(select sum(materia.valtotal) from matricula inner join materia on matricula.codmate=materia.codmate where matricula.idestu=estudiante.idestu); Query OK, 1 row affected, 4 warnings (0.085 sec) Rows matched: 5 Changed: 1 Warnings: 4 MariaDB [matriculauni]> select * from estudiante; +--------+-----------------+------------+----------------------------+--------------+ | idestu | nomestu | fechanaci | programa | valmatricula | +--------+-----------------+------------+----------------------------+--------------+ | 001 | Juan P‚rez | 2000-05-15 | Ingenier¡a Inform tica | 960000 | | 002 | Mar¡a Garc¡a | 2001-10-20 | Administraci¢n de Empresas | 0 | | 003 | Carlos Mart¡nez | 1999-03-08 | Psicolog¡a | 0 | | 004 | Ana L¢pez | 2002-08-25 | Medicina | 0 | | 005 | Pedro Ram¡rez | 2000-12-10 | Derecho | 0 | +--------+-----------------+------------+----------------------------+--------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> drop table matricula; Query OK, 0 rows affected (0.194 sec) MariaDB [matriculauni]> select * from matricula; ERROR 1146 (42S02): Table 'matriculauni.matricula' doesn't exist MariaDB [matriculauni]> create table matricula -> -> (idestu varchar(4) not null, -> -> codmate varchar(4) not null, -> -> foreign key (idestu) references estudiante(idestu) on delete cascade on update cascade, -> -> foreign key (codmate) references materia(codmate) on delete cascade on update cascade); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-> (idestu varchar(4) not null, -> codmate varchar(4) not null, -> fo...' at line 2 MariaDB [matriculauni]> create table matricula -> -> (idestu varchar(4) not null, -> -> codmate varchar(4) not null, -> -> foreign key (idestu) references estudiante(idestu) on delete cascade on update cascade, -> -> foreign key (codmate) references materia(codmate) on delete cascade on update cascade); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-> (idestu varchar(4) not null, -> codmate varchar(4) not null, -> fo...' at line 2 MariaDB [matriculauni]> create table matricula(idestu varchar(4) not null,codmate varchar(4) not null,foreign key (idestu) references estudiante(idestu) on delete cascade on update cascade,foreign key (codmate) references materia(codmate) on delete cascade on update cascade); Query OK, 0 rows affected (0.260 sec) MariaDB [matriculauni]> delete table matricula; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table matricula' at line 1 MariaDB [matriculauni]> delete matricula; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 MariaDB [matriculauni]> delete from matricula; Query OK, 0 rows affected (0.002 sec) MariaDB [matriculauni]> update estudiante set valmatricula=0 where idestu="001"; Query OK, 1 row affected (0.080 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [matriculauni]> select * from estudiante; +--------+-----------------+------------+----------------------------+--------------+ | idestu | nomestu | fechanaci | programa | valmatricula | +--------+-----------------+------------+----------------------------+--------------+ | 001 | Juan P‚rez | 2000-05-15 | Ingenier¡a Inform tica | 0 | | 002 | Mar¡a Garc¡a | 2001-10-20 | Administraci¢n de Empresas | 0 | | 003 | Carlos Mart¡nez | 1999-03-08 | Psicolog¡a | 0 | | 004 | Ana L¢pez | 2002-08-25 | Medicina | 0 | | 005 | Pedro Ram¡rez | 2000-12-10 | Derecho | 0 | +--------+-----------------+------------+----------------------------+--------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> delimiter // MariaDB [matriculauni]> creater trigger actulizar_valmatricula after insert on matricula -> for each row -> begin -> update estudiante set valmatricula=(select sum(materia.valtotal) from matricula inner join materia on matricula.codmate=materia.codmate where matricula.idestu=estudiante.idestu); -> end -> // ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'creater trigger actulizar_valmatricula after insert on matricula for each row...' at line 1 MariaDB [matriculauni]> delimiter // MariaDB [matriculauni]> create trigger actulizar_valmatricula after insert on matricula -> for each row -> begin -> update estudiante set valmatricula=(select sum(materia.valtotal) from matricula inner join materia on matricula.codmate=materia.codmate where matricula.idestu=estudiante.idestu); -> end -> // Query OK, 0 rows affected (0.104 sec) MariaDB [matriculauni]> delimiter ; MariaDB [matriculauni]> show triggers; +------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actulizar_valmatricula | INSERT | matricula | begin update estudiante set valmatricula=(select sum(materia.valtotal) from matricula inner join materia on matricula.codmate=materia.codmate where matricula.idestu=estudiante.idestu); end | AFTER | 2024-03-03 17:13:34.22 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 2 rows in set (0.026 sec) MariaDB [matriculauni]> insert into matricula values("001","001"); Query OK, 1 row affected (0.078 sec) MariaDB [matriculauni]> select * from estudiante; +--------+-----------------+------------+----------------------------+--------------+ | idestu | nomestu | fechanaci | programa | valmatricula | +--------+-----------------+------------+----------------------------+--------------+ | 001 | Juan P‚rez | 2000-05-15 | Ingenier¡a Inform tica | 600000 | | 002 | Mar¡a Garc¡a | 2001-10-20 | Administraci¢n de Empresas | 0 | | 003 | Carlos Mart¡nez | 1999-03-08 | Psicolog¡a | 0 | | 004 | Ana L¢pez | 2002-08-25 | Medicina | 0 | | 005 | Pedro Ram¡rez | 2000-12-10 | Derecho | 0 | +--------+-----------------+------------+----------------------------+--------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values("001","002"); Query OK, 1 row affected (0.053 sec) MariaDB [matriculauni]> select * from estudiante; +--------+-----------------+------------+----------------------------+--------------+ | idestu | nomestu | fechanaci | programa | valmatricula | +--------+-----------------+------------+----------------------------+--------------+ | 001 | Juan P‚rez | 2000-05-15 | Ingenier¡a Inform tica | 960000 | | 002 | Mar¡a Garc¡a | 2001-10-20 | Administraci¢n de Empresas | 0 | | 003 | Carlos Mart¡nez | 1999-03-08 | Psicolog¡a | 0 | | 004 | Ana L¢pez | 2002-08-25 | Medicina | 0 | | 005 | Pedro Ram¡rez | 2000-12-10 | Derecho | 0 | +--------+-----------------+------------+----------------------------+--------------+ 5 rows in set (0.001 sec) MariaDB [matriculauni]> select * from materia; +---------+--------------------------------+--------------+------------+----------+ | codmate | nommate | cantcreditos | valcredito | valtotal | +---------+--------------------------------+--------------+------------+----------+ | 001 | Introducci¢n a la Programaci¢n | 4 | 150000 | 600000 | | 002 | Contabilidad B sica | 3 | 120000 | 360000 | | 003 | Psicolog¡a General | 5 | 140000 | 700000 | | 004 | Anatom¡a Humana | 6 | 180000 | 1080000 | | 005 | Derecho Civil | 4 | 160000 | 640000 | +---------+--------------------------------+--------------+------------+----------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values("002","002"); Query OK, 1 row affected (0.082 sec) MariaDB [matriculauni]> select * from estudiante; +--------+-----------------+------------+----------------------------+--------------+ | idestu | nomestu | fechanaci | programa | valmatricula | +--------+-----------------+------------+----------------------------+--------------+ | 001 | Juan P‚rez | 2000-05-15 | Ingenier¡a Inform tica | 960000 | | 002 | Mar¡a Garc¡a | 2001-10-20 | Administraci¢n de Empresas | 360000 | | 003 | Carlos Mart¡nez | 1999-03-08 | Psicolog¡a | 0 | | 004 | Ana L¢pez | 2002-08-25 | Medicina | 0 | | 005 | Pedro Ram¡rez | 2000-12-10 | Derecho | 0 | +--------+-----------------+------------+----------------------------+--------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values("003","002"); Query OK, 1 row affected (0.122 sec) MariaDB [matriculauni]> select * from estudiante; +--------+-----------------+------------+----------------------------+--------------+ | idestu | nomestu | fechanaci | programa | valmatricula | +--------+-----------------+------------+----------------------------+--------------+ | 001 | Juan P‚rez | 2000-05-15 | Ingenier¡a Inform tica | 960000 | | 002 | Mar¡a Garc¡a | 2001-10-20 | Administraci¢n de Empresas | 360000 | | 003 | Carlos Mart¡nez | 1999-03-08 | Psicolog¡a | 360000 | | 004 | Ana L¢pez | 2002-08-25 | Medicina | 0 | | 005 | Pedro Ram¡rez | 2000-12-10 | Derecho | 0 | +--------+-----------------+------------+----------------------------+--------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values("003","003"); Query OK, 1 row affected (0.053 sec) MariaDB [matriculauni]> select * from estudiante; +--------+-----------------+------------+----------------------------+--------------+ | idestu | nomestu | fechanaci | programa | valmatricula | +--------+-----------------+------------+----------------------------+--------------+ | 001 | Juan P‚rez | 2000-05-15 | Ingenier¡a Inform tica | 960000 | | 002 | Mar¡a Garc¡a | 2001-10-20 | Administraci¢n de Empresas | 360000 | | 003 | Carlos Mart¡nez | 1999-03-08 | Psicolog¡a | 1060000 | | 004 | Ana L¢pez | 2002-08-25 | Medicina | 0 | | 005 | Pedro Ram¡rez | 2000-12-10 | Derecho | 0 | +--------+-----------------+------------+----------------------------+--------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values("004","004"); Query OK, 1 row affected (0.161 sec) MariaDB [matriculauni]> insert into matricula values("005","005"); Query OK, 1 row affected (0.060 sec) MariaDB [matriculauni]> select * from estudiante; +--------+-----------------+------------+----------------------------+--------------+ | idestu | nomestu | fechanaci | programa | valmatricula | +--------+-----------------+------------+----------------------------+--------------+ | 001 | Juan P‚rez | 2000-05-15 | Ingenier¡a Inform tica | 960000 | | 002 | Mar¡a Garc¡a | 2001-10-20 | Administraci¢n de Empresas | 360000 | | 003 | Carlos Mart¡nez | 1999-03-08 | Psicolog¡a | 1060000 | | 004 | Ana L¢pez | 2002-08-25 | Medicina | 1080000 | | 005 | Pedro Ram¡rez | 2000-12-10 | Derecho | 640000 | +--------+-----------------+------------+----------------------------+--------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> exit MariaDB [(none)]> show databases; +---------------------+ | Database | +---------------------+ | bshop | | ejautoincrementable | | information_schema | | libreria | | matriculasecundaria | | matriculauni | | mysql | | performance_schema | | phpmyadmin | | practicaf | | reto2 | | test | | turismo | | universidad | +---------------------+ 14 rows in set (0.029 sec) MariaDB [(none)]> use matriculauni; Database changed MariaDB [matriculauni]> show tables; +------------------------+ | Tables_in_matriculauni | +------------------------+ | estudiante | | materia | | matricula | +------------------------+ 3 rows in set (0.001 sec) MariaDB [matriculauni]> show triggers; +------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actulizar_valmatricula | INSERT | matricula | begin update estudiante set valmatricula=(select sum(materia.valtotal) from matricula inner join materia on matricula.codmate=materia.codmate where matricula.idestu=estudiante.idestu); end | AFTER | 2024-03-03 17:13:34.22 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 2 rows in set (0.038 sec) MariaDB [matriculauni]> show tables; +------------------------+ | Tables_in_matriculauni | +------------------------+ | estudiante | | materia | | matricula | +------------------------+ 3 rows in set (0.001 sec) MariaDB [matriculauni]> select * from matricula; +--------+---------+ | idestu | codmate | +--------+---------+ | 001 | 001 | | 001 | 002 | | 002 | 002 | | 003 | 002 | | 003 | 003 | | 004 | 004 | | 005 | 005 | +--------+---------+ 7 rows in set (0.166 sec) MariaDB [matriculauni]> describe matricula; +---------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------+------+-----+---------+-------+ | idestu | varchar(4) | NO | MUL | NULL | | | codmate | varchar(4) | NO | MUL | NULL | | +---------+------------+------+-----+---------+-------+ 2 rows in set (0.020 sec) MariaDB [matriculauni]> alter table matricula add estadomate varchar(20) not null; Query OK, 0 rows affected (0.089 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [matriculauni]> describe matricula; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | idestu | varchar(4) | NO | MUL | NULL | | | codmate | varchar(4) | NO | MUL | NULL | | | estadomate | varchar(20) | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.026 sec) MariaDB [matriculauni]> select * from matricula; +--------+---------+------------+ | idestu | codmate | estadomate | +--------+---------+------------+ | 001 | 001 | | | 001 | 002 | | | 002 | 002 | | | 003 | 002 | | | 003 | 003 | | | 004 | 004 | | | 005 | 005 | | +--------+---------+------------+ 7 rows in set (0.000 sec) MariaDB [matriculauni]> delete matricula; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 MariaDB [matriculauni]> delete table matricula; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table matricula' at line 1 MariaDB [matriculauni]> drop from matricula; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from matricula' at line 1 MariaDB [matriculauni]> delete from matricula; Query OK, 7 rows affected (0.094 sec) MariaDB [matriculauni]> select * from matricula; Empty set (0.000 sec) MariaDB [matriculauni]> select * from estudiante; +--------+-----------------+------------+----------------------------+--------------+ | idestu | nomestu | fechanaci | programa | valmatricula | +--------+-----------------+------------+----------------------------+--------------+ | 001 | Juan P‚rez | 2000-05-15 | Ingenier¡a Inform tica | 960000 | | 002 | Mar¡a Garc¡a | 2001-10-20 | Administraci¢n de Empresas | 360000 | | 003 | Carlos Mart¡nez | 1999-03-08 | Psicolog¡a | 1060000 | | 004 | Ana L¢pez | 2002-08-25 | Medicina | 1080000 | | 005 | Pedro Ram¡rez | 2000-12-10 | Derecho | 640000 | +--------+-----------------+------------+----------------------------+--------------+ 5 rows in set (0.033 sec) MariaDB [matriculauni]> drop from estudainte; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from estudainte' at line 1 MariaDB [matriculauni]> delete from estudiante; Query OK, 5 rows affected (0.078 sec) MariaDB [matriculauni]> select * from estudiante; Empty set (0.000 sec) MariaDB [matriculauni]> alter table drop from estudainte valmatricula; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'drop from estudainte valmatricula' at line 1 MariaDB [matriculauni]> drop trigger actulizar_valmatricula; Query OK, 0 rows affected (0.013 sec) MariaDB [matriculauni]> show triggers; +---------------+--------+---------+-------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +---------------+--------+---------+-------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +---------------+--------+---------+-------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 1 row in set (0.066 sec) MariaDB [matriculauni]> alter table estudiante drop valmatricula; Query OK, 0 rows affected (0.173 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [matriculauni]> select * from estudiante; Empty set (0.001 sec) MariaDB [matriculauni]> describe estudiante; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | idestu | varchar(4) | NO | PRI | NULL | | | nomestu | varchar(80) | NO | | NULL | | | fechanaci | date | NO | | NULL | | | programa | varchar(60) | NO | | NULL | | +-----------+-------------+------+-----+---------+-------+ 4 rows in set (0.044 sec) MariaDB [matriculauni]> describe matricula -> ; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | idestu | varchar(4) | NO | MUL | NULL | | | codmate | varchar(4) | NO | MUL | NULL | | | estadomate | varchar(20) | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.058 sec) MariaDB [matriculauni]> drop table matricula; Query OK, 0 rows affected (0.207 sec) MariaDB [matriculauni]> select * from matricula; ERROR 1146 (42S02): Table 'matriculauni.matricula' doesn't exist MariaDB [matriculauni]> show tables; +------------------------+ | Tables_in_matriculauni | +------------------------+ | estudiante | | materia | +------------------------+ 2 rows in set (0.003 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> Create table estadomate(CodEstadomate varchar(4) not null primary key,nomEstadomate varchar(20) not null); Query OK, 0 rows affected (0.437 sec) MariaDB [matriculauni]> describe estadomate; +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | CodEstadomate | varchar(4) | NO | PRI | NULL | | | nomEstadomate | varchar(20) | NO | | NULL | | +---------------+-------------+------+-----+---------+-------+ 2 rows in set (0.018 sec) MariaDB [matriculauni]> Create table matricula (IdEstu varchar(4) not null,CodMate varchar(4) not null,CodEstadomate varchar(4) not null,foreign key(idestu) references estudiante(idestu) on delete cascade on update cascade,foreign key(codmate) references materia(codmate) on delete cascade on update cascade,foreign key(codestadomate) references estadomate(codestadomate) on delete cascade on update cascade); Query OK, 0 rows affected (0.344 sec) MariaDB [matriculauni]> describe matricula; +---------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------+------+-----+---------+-------+ | IdEstu | varchar(4) | NO | MUL | NULL | | | CodMate | varchar(4) | NO | MUL | NULL | | | CodEstadomate | varchar(4) | NO | MUL | NULL | | +---------------+------------+------+-----+---------+-------+ 3 rows in set (0.031 sec) MariaDB [matriculauni]> Create table tipopago(codtppago varchar(4) not null primary key,tppago varchar(20) not null); Query OK, 0 rows affected (0.273 sec) MariaDB [matriculauni]> describe tipopago; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | codtppago | varchar(4) | NO | PRI | NULL | | | tppago | varchar(20) | NO | | NULL | | +-----------+-------------+------+-----+---------+-------+ 2 rows in set (0.024 sec) MariaDB [matriculauni]> create table estadopago(codestadopago varchar(4) not null primary key,nomestadopago varchar(20) not null); Query OK, 0 rows affected (0.329 sec) MariaDB [matriculauni]> describe estadopago; +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | codestadopago | varchar(4) | NO | PRI | NULL | | | nomestadopago | varchar(20) | NO | | NULL | | +---------------+-------------+------+-----+---------+-------+ 2 rows in set (0.034 sec) MariaDB [matriculauni]> Create table pagomatricula(codpagomatricula varchar(4) not null primary key,idestu varchar(4) not null,codtppago varchar(4) not null,fechahora date not null,codestadopago varchar(4) not null,foreign key(idestu) references estudiante(idestu) on delete cascade on update cascade,foreign key(codtppago) references tipopago(codtppago) on delete cascade on update cascade,foreign key(codestadopago) references estadopago(codestadopago) on delete cascade on update cascade); Query OK, 0 rows affected (0.275 sec) MariaDB [matriculauni]> describe pagomatricula; +------------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------+------+-----+---------+-------+ | codpagomatricula | varchar(4) | NO | PRI | NULL | | | idestu | varchar(4) | NO | MUL | NULL | | | codtppago | varchar(4) | NO | MUL | NULL | | | fechahora | date | NO | | NULL | | | codestadopago | varchar(4) | NO | MUL | NULL | | +------------------+------------+------+-----+---------+-------+ 5 rows in set (0.012 sec) MariaDB [matriculauni]> show tables; +------------------------+ | Tables_in_matriculauni | +------------------------+ | estadomate | | estadopago | | estudiante | | materia | | matricula | | pagomatricula | | tipopago | +------------------------+ 7 rows in set (0.001 sec) MariaDB [matriculauni]> alter table estadomate add descripcion varchar(200) not null; Query OK, 0 rows affected (0.100 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [matriculauni]> describe estadomate; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | CodEstadomate | varchar(4) | NO | PRI | NULL | | | nomEstadomate | varchar(20) | NO | | NULL | | | descripcion | varchar(200) | NO | | NULL | | +---------------+--------------+------+-----+---------+-------+ 3 rows in set (0.027 sec) MariaDB [matriculauni]> INSERT INTO estadomate (CodEstadomate, nomEstadomate, descripcion) VALUES -> ('001', 'Activo', 'El estudiante est  matriculado en la materia pero a£n no ha realizado el pago.'), -> ('002', 'Inactivo', 'El estudiante no est  matriculado en la materia.'), -> ('003', 'Pendiente', 'El estudiante ha realizado el pago pero la matr¡cula est  pendiente de confirmaci¢n.'), -> ('004', 'Aprobado', 'El estudiante ha aprobado la materia.'), -> ('005', 'Reprobado', 'El estudiante ha reprobado la materia.'); Query OK, 5 rows affected (0.141 sec) Records: 5 Duplicates: 0 Warnings: 0 MariaDB [matriculauni]> select * from estadomate; +---------------+---------------+--------------------------------------------------------------------------------------+ | CodEstadomate | nomEstadomate | descripcion | +---------------+---------------+--------------------------------------------------------------------------------------+ | 001 | Activo | El estudiante est  matriculado en la materia pero a£n no ha realizado el pago. | | 002 | Inactivo | El estudiante no est  matriculado en la materia. | | 003 | Pendiente | El estudiante ha realizado el pago pero la matr¡cula est  pendiente de confirmaci¢n. | | 004 | Aprobado | El estudiante ha aprobado la materia. | | 005 | Reprobado | El estudiante ha reprobado la materia. | +---------------+---------------+--------------------------------------------------------------------------------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> INSERT INTO tipopago (codtppago, tppago) VALUES -> ('001', 'Efectivo'), -> ('002', 'Tarjeta de cr‚dito'), -> ('003', 'Transferencia bancaria'), -> ('004', 'Pago en l¡nea'), -> ('005', 'Cheque'); Query OK, 5 rows affected, 1 warning (0.128 sec) Records: 5 Duplicates: 0 Warnings: 1 MariaDB [matriculauni]> select * from tipopago; +-----------+----------------------+ | codtppago | tppago | +-----------+----------------------+ | 001 | Efectivo | | 002 | Tarjeta de cr‚dito | | 003 | Transferencia bancar | | 004 | Pago en l¡nea | | 005 | Cheque | +-----------+----------------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> INSERT INTO estudiante (idestu, nomestu, fechanaci, programa) VALUES -> ('001', 'Juan P‚rez', '2000-05-15', 'Ingenier¡a Inform tica'), -> ('002', 'Mar¡a Garc¡a', '2001-10-20', 'Administraci¢n de Empresas'), -> ('003', 'Carlos Mart¡nez', '1999-03-08', 'Psicolog¡a'), -> ('004', 'Ana L¢pez', '2002-08-25', 'Medicina'), -> ('005', 'Pedro Ram¡rez', '2000-12-10', 'Derecho'); Query OK, 5 rows affected (0.100 sec) Records: 5 Duplicates: 0 Warnings: 0 MariaDB [matriculauni]> select * from estudiante; +--------+-----------------+------------+----------------------------+ | idestu | nomestu | fechanaci | programa | +--------+-----------------+------------+----------------------------+ | 001 | Juan P‚rez | 2000-05-15 | Ingenier¡a Inform tica | | 002 | Mar¡a Garc¡a | 2001-10-20 | Administraci¢n de Empresas | | 003 | Carlos Mart¡nez | 1999-03-08 | Psicolog¡a | | 004 | Ana L¢pez | 2002-08-25 | Medicina | | 005 | Pedro Ram¡rez | 2000-12-10 | Derecho | +--------+-----------------+------------+----------------------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> Trigger -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Trigger' at line 1 MariaDB [matriculauni]> describe pagomatricula; +------------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------+------+-----+---------+-------+ | codpagomatricula | varchar(4) | NO | PRI | NULL | | | idestu | varchar(4) | NO | MUL | NULL | | | codtppago | varchar(4) | NO | MUL | NULL | | | fechahora | date | NO | | NULL | | | codestadopago | varchar(4) | NO | MUL | NULL | | +------------------+------------+------+-----+---------+-------+ 5 rows in set (0.011 sec) MariaDB [matriculauni]> alter table pagomatricula add valmatricula int(10) not null; Query OK, 0 rows affected (0.117 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [matriculauni]> describe pagomatricula; +------------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------+------+-----+---------+-------+ | codpagomatricula | varchar(4) | NO | PRI | NULL | | | idestu | varchar(4) | NO | MUL | NULL | | | codtppago | varchar(4) | NO | MUL | NULL | | | fechahora | date | NO | | NULL | | | codestadopago | varchar(4) | NO | MUL | NULL | | | valmatricula | int(10) | NO | | NULL | | +------------------+------------+------+-----+---------+-------+ 6 rows in set (0.018 sec) MariaDB [matriculauni]> INSERT INTO pagomatricula (codpagomatricula, idestu, codtppago, fechahora, codestadopago, valmatricula) VALUES -> ('001', '001', '001', '0000-00-00', '003', 0), -> ('002', '002', '002', '0000-00-00', '003', 0), -> ('003', '003', '003', '0000-00-00', '003', 0), -> ('004', '004', '001', '0000-00-00', '003', 0), -> ('005', '005', '002', '0000-00-00', '003', 0); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`matriculauni`.`pagomatricula`, CONSTRAINT `pagomatricula_ibfk_3` FOREIGN KEY (`codestadopago`) REFERENCES `estadopago` (`codestadopago`) ON DELETE CASCADE ON UPDATE CASCADE) MariaDB [matriculauni]> describe pagomatricula; +------------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------+------+-----+---------+-------+ | codpagomatricula | varchar(4) | NO | PRI | NULL | | | idestu | varchar(4) | NO | MUL | NULL | | | codtppago | varchar(4) | NO | MUL | NULL | | | fechahora | date | NO | | NULL | | | codestadopago | varchar(4) | NO | MUL | NULL | | | valmatricula | int(10) | NO | | NULL | | +------------------+------------+------+-----+---------+-------+ 6 rows in set (0.054 sec) MariaDB [matriculauni]> delete from pagomatricula; Query OK, 0 rows affected (0.000 sec) MariaDB [matriculauni]> describe pagomatricula; +------------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------+------+-----+---------+-------+ | codpagomatricula | varchar(4) | NO | PRI | NULL | | | idestu | varchar(4) | NO | MUL | NULL | | | codtppago | varchar(4) | NO | MUL | NULL | | | fechahora | date | NO | | NULL | | | codestadopago | varchar(4) | NO | MUL | NULL | | | valmatricula | int(10) | NO | | NULL | | +------------------+------------+------+-----+---------+-------+ 6 rows in set (0.041 sec) MariaDB [matriculauni]> drop table pagomatricula; Query OK, 0 rows affected (0.224 sec) MariaDB [matriculauni]> describe pagomatricula; ERROR 1146 (42S02): Table 'matriculauni.pagomatricula' doesn't exist MariaDB [matriculauni]> Create table pagomatricula(codpagomatricula varchar(4) not null primary key,idestu varchar(4) not null,codtppago varchar(4) not null,fechahora date not null,valmatricula int(10) not null,codestadopago varchar(4) not null,foreign key(idestu) references estudiante(idestu) on delete cascade on update cascade,foreign key(codtppago) references tipopago(codtppago) on delete cascade on update cascade,foreign key(codestadopago) references estadopago(codestadopago) on delete cascade on update cascade); Query OK, 0 rows affected (0.279 sec) MariaDB [matriculauni]> describe pagomatricula; +------------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------+------+-----+---------+-------+ | codpagomatricula | varchar(4) | NO | PRI | NULL | | | idestu | varchar(4) | NO | MUL | NULL | | | codtppago | varchar(4) | NO | MUL | NULL | | | fechahora | date | NO | | NULL | | | valmatricula | int(10) | NO | | NULL | | | codestadopago | varchar(4) | NO | MUL | NULL | | +------------------+------------+------+-----+---------+-------+ 6 rows in set (0.057 sec) MariaDB [matriculauni]> INSERT INTO pagomatricula (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES -> ('001', '001', '001', '0000-00-00', 0, '003'), -> ('002', '002', '002', '0000-00-00', 0, '003'), -> ('003', '003', '003', '0000-00-00', 0, '003'), -> ('004', '004', '001', '0000-00-00', 0, '003'), -> ('005', '005', '002', '0000-00-00', 0, '003'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`matriculauni`.`pagomatricula`, CONSTRAINT `pagomatricula_ibfk_3` FOREIGN KEY (`codestadopago`) REFERENCES `estadopago` (`codestadopago`) ON DELETE CASCADE ON UPDATE CASCADE) MariaDB [matriculauni]> select * from estadopago; Empty set (0.001 sec) MariaDB [matriculauni]> INSERT INTO estadopago (codestadopago, nomestadopago) VALUES -> ('001', 'Pendiente'), -> ('002', 'Pagado'), -> ('003', 'Cancelado'); Query OK, 3 rows affected (0.064 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [matriculauni]> INSERT INTO estadopago (codestadopago, nomestadopago) VALUES -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 MariaDB [matriculauni]> select * from estadopago; +---------------+---------------+ | codestadopago | nomestadopago | +---------------+---------------+ | 001 | Pendiente | | 002 | Pagado | | 003 | Cancelado | +---------------+---------------+ 3 rows in set (0.000 sec) MariaDB [matriculauni]> INSERT INTO pagomatricula (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES -> ('001', '001', '001', '2024-03-01', 0, '001'), -> ('002', '002', '002', '2024-03-02', 0, '001'), -> ('003', '003', '003', '2024-03-03', 0, '001'), -> ('004', '004', '001', '2024-03-04', 0, '001'), -> ('005', '005', '002', '2024-03-05', 0, '001'); Query OK, 5 rows affected (0.084 sec) Records: 5 Duplicates: 0 Warnings: 0 MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 001 | 001 | 001 | 2024-03-01 | 0 | 001 | | 002 | 002 | 002 | 2024-03-02 | 0 | 001 | | 003 | 003 | 003 | 2024-03-03 | 0 | 001 | | 004 | 004 | 001 | 2024-03-04 | 0 | 001 | | 005 | 005 | 002 | 2024-03-05 | 0 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> describe pagomatricula; +------------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------+------+-----+---------+-------+ | codpagomatricula | varchar(4) | NO | PRI | NULL | | | idestu | varchar(4) | NO | MUL | NULL | | | codtppago | varchar(4) | NO | MUL | NULL | | | fechahora | date | NO | | NULL | | | valmatricula | int(10) | NO | | NULL | | | codestadopago | varchar(4) | NO | MUL | NULL | | +------------------+------------+------+-----+---------+-------+ 6 rows in set (0.016 sec) MariaDB [matriculauni]> describe estudiante; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | idestu | varchar(4) | NO | PRI | NULL | | | nomestu | varchar(80) | NO | | NULL | | | fechanaci | date | NO | | NULL | | | programa | varchar(60) | NO | | NULL | | +-----------+-------------+------+-----+---------+-------+ 4 rows in set (0.015 sec) MariaDB [matriculauni]> select * from materia; +---------+--------------------------------+--------------+------------+----------+ | codmate | nommate | cantcreditos | valcredito | valtotal | +---------+--------------------------------+--------------+------------+----------+ | 001 | Introducci¢n a la Programaci¢n | 4 | 150000 | 600000 | | 002 | Contabilidad B sica | 3 | 120000 | 360000 | | 003 | Psicolog¡a General | 5 | 140000 | 700000 | | 004 | Anatom¡a Humana | 6 | 180000 | 1080000 | | 005 | Derecho Civil | 4 | 160000 | 640000 | +---------+--------------------------------+--------------+------------+----------+ 5 rows in set (0.001 sec) MariaDB [matriculauni]> select * from matricula; Empty set (0.000 sec) MariaDB [matriculauni]> describe matricula; +---------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------+------+-----+---------+-------+ | IdEstu | varchar(4) | NO | MUL | NULL | | | CodMate | varchar(4) | NO | MUL | NULL | | | CodEstadomate | varchar(4) | NO | MUL | NULL | | +---------------+------------+------+-----+---------+-------+ 3 rows in set (0.046 sec) MariaDB [matriculauni]> insert into matricula values('001','001','003'); Query OK, 1 row affected (0.074 sec) MariaDB [matriculauni]> SELECT SUM(materia.valtotal) INTO valmatricula -> FROM materia -> WHERE materia.codmate = NEW.codmate; ERROR 1327 (42000): Undeclared variable: valmatricula MariaDB [matriculauni]> describe pagomatricula; +------------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------+------+-----+---------+-------+ | codpagomatricula | varchar(4) | NO | PRI | NULL | | | idestu | varchar(4) | NO | MUL | NULL | | | codtppago | varchar(4) | NO | MUL | NULL | | | fechahora | date | NO | | NULL | | | valmatricula | int(10) | NO | | NULL | | | codestadopago | varchar(4) | NO | MUL | NULL | | +------------------+------------+------+-----+---------+-------+ 6 rows in set (0.052 sec) MariaDB [matriculauni]> SELECT SUM(m.valtotal) AS total_matricula -> FROM matricula AS ma -> JOIN materia AS m ON ma.codmate = m.codmate -> adasdad; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'adasdad' at line 4 MariaDB [matriculauni]> SELECT SUM(m.valtotal) AS total_matricula -> FROM matricula AS ma -> JOIN materia AS m ON ma.codmate = m.codmate -> WHERE ma.idestu = '001'; +-----------------+ | total_matricula | +-----------------+ | 600000 | +-----------------+ 1 row in set (0.163 sec) MariaDB [matriculauni]> UPDATE pagomatricula -> SET valmatricula = ( -> SELECT SUM(m.valtotal) -> FROM matricula AS ma -> JOIN materia AS m ON ma.codmate = m.codmate -> WHERE ma.idestu ='001'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 6 MariaDB [matriculauni]> UPDATE pagomatricula -> SET valmatricula = ( -> SELECT SUM(m.valtotal) -> FROM matricula AS ma -> JOIN materia AS m ON ma.codmate = m.codmate -> WHERE ma.idestu = pagomatricula.idestu -> ) -> WHERE idestu = '001'; Query OK, 1 row affected (0.125 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 001 | 001 | 001 | 2024-03-01 | 600000 | 001 | | 002 | 002 | 002 | 2024-03-02 | 0 | 001 | | 003 | 003 | 003 | 2024-03-03 | 0 | 001 | | 004 | 004 | 001 | 2024-03-04 | 0 | 001 | | 005 | 005 | 002 | 2024-03-05 | 0 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> delete from pagomatricula; Query OK, 5 rows affected (0.123 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 001 | 001 | 001 | 2024-03-11 | 0 | 001 | | 002 | 002 | 002 | 2024-03-11 | 0 | 001 | | 003 | 003 | 003 | 2024-03-11 | 0 | 001 | | 004 | 004 | 001 | 2024-03-11 | 0 | 001 | | 005 | 005 | 002 | 2024-03-11 | 0 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 5 rows in set (0.000 sec) MariaDB [matriculauni]> select * from matricula; +--------+---------+---------------+ | IdEstu | CodMate | CodEstadomate | +--------+---------+---------------+ | 001 | 001 | 003 | +--------+---------+---------------+ 1 row in set (0.001 sec) MariaDB [matriculauni]> delete from matricula; Query OK, 1 row affected (0.067 sec) MariaDB [matriculauni]> select * from matricula; Empty set (0.000 sec) MariaDB [matriculauni]> insert into matricula values('001','001','003'); Query OK, 1 row affected (0.075 sec) MariaDB [matriculauni]> select * from matricula; +--------+---------+---------------+ | IdEstu | CodMate | CodEstadomate | +--------+---------+---------------+ | 001 | 001 | 003 | +--------+---------+---------------+ 1 row in set (0.001 sec) MariaDB [matriculauni]> delete from matricula; Query OK, 1 row affected (0.081 sec) MariaDB [matriculauni]> select * from matricula; Empty set (0.000 sec) MariaDB [matriculauni]> select * from estadopago; +---------------+---------------+ | codestadopago | nomestadopago | +---------------+---------------+ | 001 | Pendiente | | 002 | Pagado | | 003 | Cancelado | +---------------+---------------+ 3 rows in set (0.001 sec) MariaDB [matriculauni]> select * from estadomate; +---------------+---------------+--------------------------------------------------------------------------------------+ | CodEstadomate | nomEstadomate | descripcion | +---------------+---------------+--------------------------------------------------------------------------------------+ | 001 | Activo | El estudiante est  matriculado en la materia pero a£n no ha realizado el pago. | | 002 | Inactivo | El estudiante no est  matriculado en la materia. | | 003 | Pendiente | El estudiante ha realizado el pago pero la matr¡cula est  pendiente de confirmaci¢n. | | 004 | Aprobado | El estudiante ha aprobado la materia. | | 005 | Reprobado | El estudiante ha reprobado la materia. | +---------------+---------------+--------------------------------------------------------------------------------------+ 5 rows in set (0.001 sec) MariaDB [matriculauni]> select * from pagomatricula; Empty set (0.000 sec) MariaDB [matriculauni]> ALTER TABLE pagomatricula -> MODIFY COLUMN codpagomatricula INT AUTO_INCREMENT NOT NULL; Query OK, 0 rows affected (1.064 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [matriculauni]> select * from pagomatricula; Empty set (0.001 sec) MariaDB [matriculauni]> describe pagomatricula; +------------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------+------+-----+---------+----------------+ | codpagomatricula | int(11) | NO | PRI | NULL | auto_increment | | idestu | varchar(4) | NO | MUL | NULL | | | codtppago | varchar(4) | NO | MUL | NULL | | | fechahora | date | NO | | NULL | | | valmatricula | int(10) | NO | | NULL | | | codestadopago | varchar(4) | NO | MUL | NULL | | +------------------+------------+------+-----+---------+----------------+ 6 rows in set (0.012 sec) MariaDB [matriculauni]> select * from tipopago; +-----------+----------------------+ | codtppago | tppago | +-----------+----------------------+ | 001 | Efectivo | | 002 | Tarjeta de cr‚dito | | 003 | Transferencia bancar | | 004 | Pago en l¡nea | | 005 | Cheque | +-----------+----------------------+ 5 rows in set (0.029 sec) MariaDB [matriculauni]> select * from estadoopago; ERROR 1146 (42S02): Table 'matriculauni.estadoopago' doesn't exist MariaDB [matriculauni]> select * from estadopago; +---------------+---------------+ | codestadopago | nomestadopago | +---------------+---------------+ | 001 | Pendiente | | 002 | Pagado | | 003 | Cancelado | +---------------+---------------+ 3 rows in set (0.000 sec) MariaDB [matriculauni]> show triggers; +---------------+--------+---------+-------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +---------------+--------+---------+-------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +---------------+--------+---------+-------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 1 row in set (0.029 sec) MariaDB [matriculauni]> show tables; +------------------------+ | Tables_in_matriculauni | +------------------------+ | estadomate | | estadopago | | estudiante | | materia | | matricula | | pagomatricula | | tipopago | +------------------------+ 7 rows in set (0.001 sec) MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER actualizar_valmatricula_despues_insertar -> AFTER INSERT ON matricula -> FOR EACH ROW -> BEGIN -> DECLARE contador INT; -> -> -- Verificar si el estudiante ya tiene un registro en pagomatricula -> SELECT COUNT(*) INTO contador -> FROM pagomatricula -> WHERE idestu = NEW.idestu; -> -> -- Si el estudiante no tiene registro, crear uno nuevo -> IF contador = 0 THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si el estudiante ya tiene un registro, sumar el valor de la nueva materia a valmatricula -> UPDATE pagomatricula -> SET valmatricula = valmatricula + (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), -> fechahora = NOW() -> WHERE idestu = NEW.idestu; -> END IF; -> END; -> // Query OK, 0 rows affected (0.109 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula WHERE idestu = NEW.idestu; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 19:20:40.11 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 2 rows in set (0.012 sec) MariaDB [matriculauni]> select * from matricula; +--------+---------+---------------+ | IdEstu | CodMate | CodEstadomate | +--------+---------+---------------+ | 001 | 001 | 003 | | 001 | 002 | 003 | +--------+---------+---------------+ 2 rows in set (0.002 sec) MariaDB [matriculauni]> drop from matricula; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from matricula' at line 1 MariaDB [matriculauni]> delete from matricula; Query OK, 2 rows affected (0.081 sec) MariaDB [matriculauni]> select * from pagomatricula; Empty set (0.000 sec) MariaDB [matriculauni]> inser into matricula values('001','001','003'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'inser into matricula values('001','001','003')' at line 1 MariaDB [matriculauni]> insert into matricula values('001','001','003'); Query OK, 1 row affected (0.107 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 1 | 001 | 004 | 2024-03-11 | 600000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values('001','002','003'); Query OK, 1 row affected (0.171 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 1 | 001 | 004 | 2024-03-11 | 960000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER procesar_pago -> AFTER UPDATE ON pagomatricula -> FOR EACH ROW -> BEGIN -> -- Verificar si el estado de pago cambi¢ a '002' -> IF NEW.codestadopago = '002' THEN -> -> INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NEW.fechahora, NEW.valmatricula, NEW.codestadopago); -> -> -- Eliminar el registro de la tabla pagomatricula -> DELETE FROM pagomatricula WHERE codpagomatricula = NEW.codpagomatricula; -> -> -- Actualizar el estado de la materia en la tabla matricula -> UPDATE matricula SET codestadomate = '001' WHERE codmate = NEW.codmate AND idestu = NEW.idestu; -> END IF; -> END; -> // ERROR 1054 (42S22): Unknown column 'codmate' in 'NEW' MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula WHERE idestu = NEW.idestu; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 19:20:40.11 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 2 rows in set (0.092 sec) MariaDB [matriculauni]> describe pagomatricula; +------------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------+------+-----+---------+----------------+ | codpagomatricula | int(11) | NO | PRI | NULL | auto_increment | | idestu | varchar(4) | NO | MUL | NULL | | | codtppago | varchar(4) | NO | MUL | NULL | | | fechahora | date | NO | | NULL | | | valmatricula | int(10) | NO | | NULL | | | codestadopago | varchar(4) | NO | MUL | NULL | | +------------------+------------+------+-----+---------+----------------+ 6 rows in set (0.016 sec) MariaDB [matriculauni]> Create table historialpago -> (codpagomatricula varchar(10) not null primary key, -> idestu varchar(4) not null, -> codtppago varchar(4) not null, -> fechahora date not null, -> valmatricula int(10) not null, -> codestadopago varchar(4) not null); Query OK, 0 rows affected (0.284 sec) MariaDB [matriculauni]> describe historialpago; +------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+---------+-------+ | codpagomatricula | varchar(10) | NO | PRI | NULL | | | idestu | varchar(4) | NO | | NULL | | | codtppago | varchar(4) | NO | | NULL | | | fechahora | date | NO | | NULL | | | valmatricula | int(10) | NO | | NULL | | | codestadopago | varchar(4) | NO | | NULL | | +------------------+-------------+------+-----+---------+-------+ 6 rows in set (0.021 sec) MariaDB [matriculauni]> drop from historialmatricula; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from historialmatricula' at line 1 MariaDB [matriculauni]> drop table historialmatricula; ERROR 1051 (42S02): Unknown table 'matriculauni.historialmatricula' MariaDB [matriculauni]> drop table historialpago; Query OK, 0 rows affected (0.186 sec) MariaDB [matriculauni]> describe historialpago; ERROR 1146 (42S02): Table 'matriculauni.historialpago' doesn't exist MariaDB [matriculauni]> Create table historialpago -> (codpagomatricula varchar(10) not null primary key, -> idestu varchar(4) not null, -> codtppago varchar(4) not null, -> fechahora date not null, -> valmatricula int(10) not null, -> codestadopago varchar(4) not null); Query OK, 0 rows affected (0.197 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER procesar_pago -> AFTER UPDATE ON pagomatricula -> FOR EACH ROW -> BEGIN -> -- Verificar si el estado de pago cambi¢ a '002' -> IF NEW.codestadopago = '002' THEN -> -> INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NEW.fechahora, NEW.valmatricula, NEW.codestadopago); -> -> -- Eliminar el registro de la tabla pagomatricula -> DELETE FROM pagomatricula WHERE codpagomatricula = NEW.codpagomatricula; -> -> -- Actualizar el estado de la materia en la tabla matricula -> UPDATE matricula SET codestadomate = '001' WHERE codmate = NEW.codmate AND idestu = NEW.idestu; -> END IF; -> END; -> // ERROR 1054 (42S22): Unknown column 'codmate' in 'NEW' MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> describe matricula; +---------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------+------+-----+---------+-------+ | IdEstu | varchar(4) | NO | MUL | NULL | | | CodMate | varchar(4) | NO | MUL | NULL | | | CodEstadomate | varchar(4) | NO | MUL | NULL | | +---------------+------------+------+-----+---------+-------+ 3 rows in set (0.014 sec) MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER procesar_pago -> AFTER UPDATE ON pagomatricula -> FOR EACH ROW -> BEGIN -> -- Verificar si el estado de pago cambi¢ a '002' -> IF NEW.codestadopago = '002' THEN -> -- Insertar informaci¢n del pago en la tabla historialpago -> INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); -> -> -- Eliminar el registro de la tabla pagomatricula -> DELETE FROM pagomatricula WHERE codpagomatricula = NEW.codpagomatricula; -> -> -- Actualizar el estado de la materia en la tabla matricula -> UPDATE matricula -> SET codestadomate = '001' -> WHERE codmate = (SELECT codmate FROM matricula WHERE idestu = NEW.idestu LIMIT 1) -> AND idestu = NEW.idestu; -> END IF; -> END; -> // Query OK, 0 rows affected (0.087 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula WHERE idestu = NEW.idestu; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 19:20:40.11 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | procesar_pago | UPDATE | pagomatricula | BEGIN IF NEW.codestadopago = '002' THEN INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); DELETE FROM pagomatricula WHERE codpagomatricula = NEW.codpagomatricula; UPDATE matricula SET codestadomate = '001' WHERE codmate = (SELECT codmate FROM matricula WHERE idestu = NEW.idestu LIMIT 1) AND idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 20:05:02.07 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.036 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 1 | 001 | 004 | 2024-03-11 | 960000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.002 sec) MariaDB [matriculauni]> update pagomatricula set codestado='002' where codpagomatricula=1; ERROR 1054 (42S22): Unknown column 'codestado' in 'field list' MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula=1; ERROR 1442 (HY000): Can't update table 'pagomatricula' in stored function/trigger because it is already used by statement which invoked this stored function/trigger MariaDB [matriculauni]> drop trigger procesar_pago; Query OK, 0 rows affected (0.002 sec) MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER procesar_pago_insert -> AFTER UPDATE ON pagomatricula -> FOR EACH ROW -> BEGIN -> -- Verificar si el estado de pago cambi¢ a '002' -> IF NEW.codestadopago = '002' THEN -> -- Insertar informaci¢n del pago en la tabla historialpago -> INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); -> END IF; -> END; -> // Query OK, 0 rows affected (0.083 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER procesar_pago_delete_update -> AFTER UPDATE ON pagomatricula -> FOR EACH ROW -> BEGIN -> -- Verificar si el estado de pago cambi¢ a '002' -> IF NEW.codestadopago = '002' THEN -> -- Eliminar el registro de la tabla pagomatricula -> DELETE FROM pagomatricula WHERE codpagomatricula = NEW.codpagomatricula; -> -> -- Actualizar el estado de la materia en la tabla matricula -> UPDATE matricula -> SET codestadomate = '001' -> WHERE codmate = (SELECT codmate FROM matricula WHERE idestu = NEW.idestu LIMIT 1) -> AND idestu = NEW.idestu; -> END IF; -> END; -> // Query OK, 0 rows affected (0.148 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula WHERE idestu = NEW.idestu; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 19:20:40.11 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | procesar_pago_insert | UPDATE | pagomatricula | BEGIN IF NEW.codestadopago = '002' THEN INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); END IF; END | AFTER | 2024-03-11 20:13:59.41 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | procesar_pago_delete_update | UPDATE | pagomatricula | BEGIN IF NEW.codestadopago = '002' THEN DELETE FROM pagomatricula WHERE codpagomatricula = NEW.codpagomatricula; UPDATE matricula SET codestadomate = '001' WHERE codmate = (SELECT codmate FROM matricula WHERE idestu = NEW.idestu LIMIT 1) AND idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 20:13:59.53 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 4 rows in set (0.047 sec) MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula='1'; ERROR 1442 (HY000): Can't update table 'pagomatricula' in stored function/trigger because it is already used by statement which invoked this stored function/trigger MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula=1; ERROR 1442 (HY000): Can't update table 'pagomatricula' in stored function/trigger because it is already used by statement which invoked this stored function/trigger MariaDB [matriculauni]> select * from estadopago; +---------------+---------------+ | codestadopago | nomestadopago | +---------------+---------------+ | 001 | Pendiente | | 002 | Pagado | | 003 | Cancelado | +---------------+---------------+ 3 rows in set (0.001 sec) MariaDB [matriculauni]> drop trigger procesar_pago_delete_update; Query OK, 0 rows affected (0.063 sec) MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula WHERE idestu = NEW.idestu; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 19:20:40.11 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | procesar_pago_insert | UPDATE | pagomatricula | BEGIN IF NEW.codestadopago = '002' THEN INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); END IF; END | AFTER | 2024-03-11 20:13:59.41 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.022 sec) MariaDB [matriculauni]> drop trigger procesar_pago_insert; Query OK, 0 rows affected (0.003 sec) MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula WHERE idestu = NEW.idestu; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 19:20:40.11 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 2 rows in set (0.013 sec) MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER procesar_pago -> AFTER UPDATE ON pagomatricula -> FOR EACH ROW -> BEGIN -> -- Verificar si el estado de pago cambi¢ a '002' -> IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' THEN -> -- Insertar informaci¢n del pago en la tabla historialpago -> INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); -> -> -- Eliminar el registro de la tabla pagomatricula -> DELETE FROM pagomatricula WHERE codpagomatricula = NEW.codpagomatricula; -> -> -- Actualizar el estado de la materia en la tabla matricula -> UPDATE matricula -> SET codestadomate = '001' -> WHERE codmate = (SELECT codmate FROM matricula WHERE idestu = NEW.idestu LIMIT 1) -> AND idestu = NEW.idestu; -> END IF; -> END; -> // Query OK, 0 rows affected (0.130 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula WHERE idestu = NEW.idestu; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 19:20:40.11 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | procesar_pago | UPDATE | pagomatricula | BEGIN IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' THEN INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); DELETE FROM pagomatricula WHERE codpagomatricula = NEW.codpagomatricula; UPDATE matricula SET codestadomate = '001' WHERE codmate = (SELECT codmate FROM matricula WHERE idestu = NEW.idestu LIMIT 1) AND idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 20:21:24.89 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.081 sec) MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula='1'; ERROR 1442 (HY000): Can't update table 'pagomatricula' in stored function/trigger because it is already used by statement which invoked this stored function/trigger MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula=1; ERROR 1442 (HY000): Can't update table 'pagomatricula' in stored function/trigger because it is already used by statement which invoked this stored function/trigger MariaDB [matriculauni]> drop trigger procesar_pago; Query OK, 0 rows affected (0.002 sec) MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER procesar_pago -> AFTER UPDATE ON pagomatricula -> FOR EACH ROW -> BEGIN -> -- Verificar si el estado de pago cambi¢ a '002' -> IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' THEN -> -- Insertar informaci¢n del pago en la tabla historialpago -> INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); -> -> -- Eliminar el registro de la tabla pagomatricula -> DELETE FROM pagomatricula WHERE codpagomatricula = NEW.codpagomatricula; -> -> -- Actualizar el estado de la materia en la tabla matricula -> UPDATE matricula -> SET codestadomate = '001' -> WHERE idestu = NEW.idestu AND codmate = (SELECT codmate FROM matricula WHERE idestu = NEW.idestu LIMIT 1); -> END IF; -> END; -> // Query OK, 0 rows affected (0.066 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula='1'; ERROR 1442 (HY000): Can't update table 'pagomatricula' in stored function/trigger because it is already used by statement which invoked this stored function/trigger MariaDB [matriculauni]> drop trigger procesar_pago; Query OK, 0 rows affected (0.002 sec) MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER procesar_pago -> AFTER UPDATE ON pagomatricula -> FOR EACH ROW -> BEGIN -> -- Verificar si el estado de pago cambi¢ a '002' y la actualizaci¢n no es realizada por la consulta espec¡fica -> IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' AND @disable_trigger IS NULL THEN -> -- Tu l¢gica de trigger aqu¡ -> -- Insertar informaci¢n del pago en la tabla historialpago -> INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); -> -> -- Eliminar el registro de la tabla pagomatricula -> DELETE FROM pagomatricula WHERE codpagomatricula = NEW.codpagomatricula; -> -> -- Actualizar el estado de la materia en la tabla matricula -> UPDATE matricula -> SET codestadomate = '001' -> WHERE idestu = NEW.idestu AND codmate = (SELECT codmate FROM matricula WHERE idestu = NEW.idestu LIMIT 1); -> END IF; -> END; -> // Query OK, 0 rows affected (0.166 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> MariaDB [matriculauni]> UPDATE pagomatricula SET codestadopago='002' WHERE codpagomatricula='1'; ERROR 1442 (HY000): Can't update table 'pagomatricula' in stored function/trigger because it is already used by statement which invoked this stored function/trigger MariaDB [matriculauni]> drop trigger procesar_pago; Query OK, 0 rows affected (0.002 sec) MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula=1; Query OK, 1 row affected (0.072 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 1 | 001 | 004 | 2024-03-11 | 960000 | 002 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> update pagomatricula set codestadopago='001' where codpagomatricula=1; Query OK, 1 row affected (0.039 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [matriculauni]> update pagomatricula set codestadopago='001' where codpagomatricula=1; Query OK, 0 rows affected (0.000 sec) Rows matched: 1 Changed: 0 Warnings: 0 MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 1 | 001 | 004 | 2024-03-11 | 960000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER procesar_pago -> AFTER UPDATE ON pagomatricula -> FOR EACH ROW -> BEGIN -> -- Verificar si el estado de pago cambi¢ a '002' -> IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' THEN -> -- Insertar informaci¢n del pago en la tabla historialpago -> INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); -> -> -- Actualizar el estado de las materias en la tabla matricula a '001' -> UPDATE matricula -> SET codestadomate = '001' -> WHERE idestu = NEW.idestu; -> -> -- No es necesario eliminar el registro de la tabla pagomatricula, ya que se est  actualizando el campo codestadopago -> END IF; -> END; -> // Query OK, 0 rows affected (0.093 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula WHERE idestu = NEW.idestu; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 19:20:40.11 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | procesar_pago | UPDATE | pagomatricula | BEGIN IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' THEN INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); UPDATE matricula SET codestadomate = '001' WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 20:38:27.69 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.043 sec) MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula=1; Query OK, 1 row affected (0.194 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [matriculauni]> select * from histroialpago; ERROR 1146 (42S02): Table 'matriculauni.histroialpago' doesn't exist MariaDB [matriculauni]> select * from histrorialpago; ERROR 1146 (42S02): Table 'matriculauni.histrorialpago' doesn't exist MariaDB [matriculauni]> select * from historialpago; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 1 | 001 | 004 | 2024-03-11 | 960000 | 002 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> show triggers ; +------------------------------------------+--------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula WHERE idestu = NEW.idestu; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 19:20:40.11 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | procesar_pago | UPDATE | pagomatricula | BEGIN IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' THEN INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); UPDATE matricula SET codestadomate = '001' WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 20:38:27.69 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.015 sec) MariaDB [matriculauni]> drop trigger actualizar_valmatricula_despues_insertar; Query OK, 0 rows affected (0.002 sec) MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER actualizar_valmatricula_despues_insertar -> AFTER INSERT ON matricula -> FOR EACH ROW -> BEGIN -> DECLARE contador INT; -> -> -- Contar cu ntos registros existen en pagomatricula para el nuevo estudiante -> SELECT COUNT(*) INTO contador -> FROM pagomatricula -> WHERE idestu = NEW.idestu; -> -> -- Si no hay registros en pagomatricula para el nuevo estudiante, insertar uno nuevo -> IF contador = 0 THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si hay registros en pagomatricula para el nuevo estudiante -> -- y el estado del codestadopago es '002', insertar un nuevo registro -> IF NEW.codestadopago = '002' THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si el estado del codestadopago no es '002', actualizar el registro existente -> UPDATE pagomatricula -> SET valmatricula = valmatricula + (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), -> fechahora = NOW() -> WHERE idestu = NEW.idestu; -> END IF; -> END IF; -> END; -> // ERROR 1054 (42S22): Unknown column 'codestadopago' in 'NEW' MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER actualizar_valmatricula_despues_insertar -> AFTER INSERT ON matricula -> FOR EACH ROW -> BEGIN -> DECLARE contador INT; -> -> -- Contar cu ntos registros existen en pagomatricula para el nuevo estudiante -> SELECT COUNT(*) INTO contador -> FROM pagomatricula -> WHERE idestu = NEW.idestu; -> -> -- Si no hay registros en pagomatricula para el nuevo estudiante, insertar uno nuevo -> IF contador = 0 THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si hay registros en pagomatricula para el nuevo estudiante -> -- y el estado del codestadopago es '002', insertar un nuevo registro -> IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si el estado del codestadopago no es '002', actualizar el registro existente -> UPDATE pagomatricula -> SET valmatricula = valmatricula + (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), -> fechahora = NOW() -> WHERE idestu = NEW.idestu; -> END IF; -> END IF; -> END; -> // Query OK, 0 rows affected (0.095 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula WHERE idestu = NEW.idestu; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END IF; END | AFTER | 2024-03-11 20:49:33.57 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | procesar_pago | UPDATE | pagomatricula | BEGIN IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' THEN INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); UPDATE matricula SET codestadomate = '001' WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 20:38:27.69 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.013 sec) MariaDB [matriculauni]> select * from historialpago -> ; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 1 | 001 | 004 | 2024-03-11 | 960000 | 002 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> delete from historialpago; Query OK, 1 row affected (0.078 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 1 | 001 | 004 | 2024-03-11 | 960000 | 002 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> delete from pagomatricula; Query OK, 1 row affected (0.056 sec) MariaDB [matriculauni]> select * from matricula; +--------+---------+---------------+ | IdEstu | CodMate | CodEstadomate | +--------+---------+---------------+ | 001 | 001 | 001 | | 001 | 002 | 001 | +--------+---------+---------------+ 2 rows in set (0.002 sec) MariaDB [matriculauni]> delete from matricula; Query OK, 2 rows affected (0.076 sec) MariaDB [matriculauni]> insert into matricula values('001','001','003'); Query OK, 1 row affected (0.098 sec) MariaDB [matriculauni]> select * from matricula; +--------+---------+---------------+ | IdEstu | CodMate | CodEstadomate | +--------+---------+---------------+ | 001 | 001 | 003 | +--------+---------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 2 | 001 | 004 | 2024-03-11 | 600000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values('001','002','003'); Query OK, 1 row affected (0.164 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 2 | 001 | 004 | 2024-03-11 | 960000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> update from pagomatricula -> set codestadopago='002' where codpagomatricula='2'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from pagomatricula set codestadopago='002' where codpagomatricula='2'' at line 1 MariaDB [matriculauni]> MariaDB [matriculauni]> select * from matricula; +--------+---------+---------------+ | IdEstu | CodMate | CodEstadomate | +--------+---------+---------------+ | 001 | 001 | 003 | | 001 | 002 | 003 | +--------+---------+---------------+ 2 rows in set (0.001 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 2 | 001 | 004 | 2024-03-11 | 960000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula='2'; Query OK, 1 row affected (0.067 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 2 | 001 | 004 | 2024-03-11 | 960000 | 002 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.040 sec) MariaDB [matriculauni]> select * from historialpago; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 2 | 001 | 004 | 2024-03-11 | 960000 | 002 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values('001','001','003'); Query OK, 1 row affected (0.089 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 2 | 001 | 004 | 2024-03-11 | 960000 | 002 | | 3 | 001 | 004 | 2024-03-11 | 600000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 2 rows in set (0.000 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 2 | 001 | 004 | 2024-03-11 | 960000 | 002 | | 3 | 001 | 004 | 2024-03-11 | 600000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 2 rows in set (0.002 sec) MariaDB [matriculauni]> exit; MariaDB [(none)]> use matriculauni; Database changed MariaDB [matriculauni]> show tables; +------------------------+ | Tables_in_matriculauni | +------------------------+ | estadomate | | estadopago | | estudiante | | historialpago | | materia | | matricula | | pagomatricula | | tipopago | +------------------------+ 8 rows in set (0.001 sec) MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula WHERE idestu = NEW.idestu; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT m.valtotal FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END IF; END | AFTER | 2024-03-11 20:49:33.57 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | procesar_pago | UPDATE | pagomatricula | BEGIN IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' THEN INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); UPDATE matricula SET codestadomate = '001' WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 20:38:27.69 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.055 sec) MariaDB [matriculauni]> select * from matricula; +--------+---------+---------------+ | IdEstu | CodMate | CodEstadomate | +--------+---------+---------------+ | 001 | 001 | 001 | | 001 | 002 | 001 | | 001 | 001 | 003 | +--------+---------+---------------+ 3 rows in set (0.001 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 2 | 001 | 004 | 2024-03-11 | 960000 | 002 | | 3 | 001 | 004 | 2024-03-11 | 600000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 2 rows in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values('002','001','003'); Query OK, 1 row affected (0.147 sec) MariaDB [matriculauni]> select * from matricula; +--------+---------+---------------+ | IdEstu | CodMate | CodEstadomate | +--------+---------+---------------+ | 001 | 001 | 001 | | 001 | 002 | 001 | | 001 | 001 | 003 | | 002 | 001 | 003 | +--------+---------+---------------+ 4 rows in set (0.000 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 2 | 001 | 004 | 2024-03-11 | 960000 | 002 | | 3 | 001 | 004 | 2024-03-11 | 600000 | 001 | | 4 | 002 | 004 | 2024-03-11 | 600000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 3 rows in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values('001','003','003'); ERROR 1242 (21000): Subquery returns more than 1 row MariaDB [matriculauni]> insert into matricula values('001','002}','003'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`matriculauni`.`matricula`, CONSTRAINT `matricula_ibfk_2` FOREIGN KEY (`CodMate`) REFERENCES `materia` (`codmate`) ON DELETE CASCADE ON UPDATE CASCADE) MariaDB [matriculauni]> insert into matricula values('001','002','003'); ERROR 1242 (21000): Subquery returns more than 1 row MariaDB [matriculauni]> insert into matricula values('002','002','003'); Query OK, 1 row affected (0.050 sec) MariaDB [matriculauni]> insert into matricula values('003','002','003'); Query OK, 1 row affected (0.046 sec) MariaDB [matriculauni]> insert into matricula values('002','003','003'); Query OK, 1 row affected (0.123 sec) MariaDB [matriculauni]> select * from matricula; +--------+---------+---------------+ | IdEstu | CodMate | CodEstadomate | +--------+---------+---------------+ | 001 | 001 | 001 | | 001 | 002 | 001 | | 001 | 001 | 003 | | 002 | 001 | 003 | | 002 | 002 | 003 | | 003 | 002 | 003 | | 002 | 003 | 003 | +--------+---------+---------------+ 7 rows in set (0.000 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 2 | 001 | 004 | 2024-03-11 | 960000 | 002 | | 3 | 001 | 004 | 2024-03-11 | 600000 | 001 | | 4 | 002 | 004 | 2024-03-11 | 1660000 | 001 | | 5 | 003 | 004 | 2024-03-11 | 360000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 4 rows in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values('001','001','003'); ERROR 1242 (21000): Subquery returns more than 1 row MariaDB [matriculauni]> drop trigger actualizar_valmatricula_despues_insertar; Query OK, 0 rows affected (0.033 sec) MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER actualizar_valmatricula_despues_insertar -> AFTER INSERT ON matricula -> FOR EACH ROW -> BEGIN -> DECLARE contador INT; -> -> -- Contar cu ntos registros existen en pagomatricula para el nuevo estudiante -> SELECT COUNT(*) INTO contador -> FROM pagomatricula -> WHERE idestu = NEW.idestu; -> -> -- Si no hay registros en pagomatricula para el nuevo estudiante, insertar uno nuevo -> IF contador = 0 THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si hay registros en pagomatricula para el nuevo estudiante y el estado del codestadopago es '002', insertar un nuevo registro -> IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si el estado del codestadopago no es '002', actualizar el registro existente -> UPDATE pagomatricula -> SET valmatricula = valmatricula + (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), -> fechahora = NOW() -> WHERE idestu = NEW.idestu; -> END IF; -> END IF; -> END; -> // Query OK, 0 rows affected (0.099 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula WHERE idestu = NEW.idestu; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END IF; END | AFTER | 2024-03-11 22:27:55.78 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | procesar_pago | UPDATE | pagomatricula | BEGIN IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' THEN INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); UPDATE matricula SET codestadomate = '001' WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 20:38:27.69 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.035 sec) MariaDB [matriculauni]> delete from matricula; Query OK, 7 rows affected (0.070 sec) MariaDB [matriculauni]> delete from pagomatricula; Query OK, 4 rows affected (0.050 sec) MariaDB [matriculauni]> delete from historialpago; Query OK, 1 row affected (0.052 sec) MariaDB [matriculauni]> insert into matricula values('001','001','003'); Query OK, 1 row affected (0.098 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 6 | 001 | 004 | 2024-03-11 | 600000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values('001','002','003'); Query OK, 1 row affected (0.135 sec) MariaDB [matriculauni]> insert into matricula values('001','003','003'); Query OK, 1 row affected (0.056 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 6 | 001 | 004 | 2024-03-11 | 1660000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula='6'; Query OK, 1 row affected (0.156 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [matriculauni]> select * from historialpago; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 6 | 001 | 004 | 2024-03-11 | 1660000 | 002 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.035 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 6 | 001 | 004 | 2024-03-11 | 1660000 | 002 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values('001','003','003'); Query OK, 1 row affected (0.066 sec) MariaDB [matriculauni]> insert into matricula values('001','002','003'); ERROR 1242 (21000): Subquery returns more than 1 row MariaDB [matriculauni]> insert into matricula values('001','004','003'); ERROR 1242 (21000): Subquery returns more than 1 row MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula WHERE idestu = NEW.idestu; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END IF; END | AFTER | 2024-03-11 22:27:55.78 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | procesar_pago | UPDATE | pagomatricula | BEGIN IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' THEN INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); UPDATE matricula SET codestadomate = '001' WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 20:38:27.69 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.024 sec) MariaDB [matriculauni]> drop trigger actualizar_valmatricula_despues_insertar; Query OK, 0 rows affected (0.002 sec) MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER actualizar_valmatricula_despues_insertar -> AFTER INSERT ON matricula -> FOR EACH ROW -> BEGIN -> DECLARE contador INT; -> -> -- Contar cu ntos registros existen en pagomatricula para el nuevo estudiante -> SELECT COUNT(*) INTO contador -> FROM pagomatricula -> WHERE idestu = NEW.idestu -> AND codestadopago <> '002'; -- No contar los registros con c¢digo de estado de pago '002' -> -> -- Si no hay registros en pagomatricula para el nuevo estudiante, insertar uno nuevo -> IF contador = 0 THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si hay registros en pagomatricula para el nuevo estudiante y el estado del codestadopago es '002', insertar un nuevo registro -> IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si el estado del codestadopago no es '002', actualizar el registro existente -> UPDATE pagomatricula -> SET valmatricula = valmatricula + (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), -> fechahora = NOW() -> WHERE idestu = NEW.idestu; -> END IF; -> END IF; -> END; -> // Query OK, 0 rows affected (0.111 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula WHERE idestu = NEW.idestu AND codestadopago <> '002'; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END IF; END | AFTER | 2024-03-11 22:39:50.12 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | procesar_pago | UPDATE | pagomatricula | BEGIN IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' THEN INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); UPDATE matricula SET codestadomate = '001' WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 20:38:27.69 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.038 sec) MariaDB [matriculauni]> delete from matricula; Query OK, 4 rows affected (0.122 sec) MariaDB [matriculauni]> delete from pagomatricula; Query OK, 2 rows affected (0.049 sec) MariaDB [matriculauni]> delete from historialpago; Query OK, 1 row affected (0.073 sec) MariaDB [matriculauni]> insert into matricula values('001','001','001'); Query OK, 1 row affected (0.130 sec) MariaDB [matriculauni]> insert into matricula values('001','001','001'); Query OK, 1 row affected (0.123 sec) MariaDB [matriculauni]> insert into matricula values('001','001','003'); Query OK, 1 row affected (0.095 sec) MariaDB [matriculauni]> insert into matricula values('001','002','003'); Query OK, 1 row affected (0.120 sec) MariaDB [matriculauni]> insert into matricula values('001','003','003'); Query OK, 1 row affected (0.051 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 8 | 001 | 004 | 2024-03-11 | 2860000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricila=8; ERROR 1054 (42S22): Unknown column 'codpagomatricila' in 'where clause' MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula=8; Query OK, 1 row affected (0.162 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 8 | 001 | 004 | 2024-03-11 | 2860000 | 002 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> select * from historialpago; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 8 | 001 | 004 | 2024-03-11 | 2860000 | 002 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values('001','001','003'); Query OK, 1 row affected (0.073 sec) MariaDB [matriculauni]> insert into matricula values('001','003','003'); ERROR 1242 (21000): Subquery returns more than 1 row MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 8 | 001 | 004 | 2024-03-11 | 2860000 | 002 | | 9 | 001 | 004 | 2024-03-11 | 600000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 2 rows in set (0.000 sec) MariaDB [matriculauni]> select * from matricula; +--------+---------+---------------+ | IdEstu | CodMate | CodEstadomate | +--------+---------+---------------+ | 001 | 001 | 001 | | 001 | 001 | 001 | | 001 | 001 | 001 | | 001 | 002 | 001 | | 001 | 003 | 001 | | 001 | 001 | 003 | +--------+---------+---------------+ 6 rows in set (0.000 sec) MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula WHERE idestu = NEW.idestu AND codestadopago <> '002'; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END IF; END | AFTER | 2024-03-11 22:39:50.12 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | procesar_pago | UPDATE | pagomatricula | BEGIN IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' THEN INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); UPDATE matricula SET codestadomate = '001' WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 20:38:27.69 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.017 sec) MariaDB [matriculauni]> drop trigger actualizar_valmatricula_despues_insertar; Query OK, 0 rows affected (0.002 sec) MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER actualizar_valmatricula_despues_insertar -> AFTER INSERT ON matricula -> FOR EACH ROW -> BEGIN -> DECLARE contador INT; -> -> -- Contar cu ntos registros existen en pagomatricula para el nuevo estudiante -> SELECT COUNT(*) INTO contador -> FROM pagomatricula AS pm -> INNER JOIN matricula AS ma ON pm.idestu = ma.idestu -> WHERE pm.idestu = NEW.idestu -> AND pm.codestadopago <> '002' -> AND ma.codestadomate <> '001'; -- No contar las materias de matr¡culas con estado '001' -> -> -- Si no hay registros en pagomatricula para el nuevo estudiante, insertar uno nuevo -> IF contador = 0 THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si hay registros en pagomatricula para el nuevo estudiante y el estado del codestadopago es '002', insertar un nuevo registro -> IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si el estado del codestadopago no es '002', actualizar el registro existente -> UPDATE pagomatricula -> SET valmatricula = valmatricula + (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), -> fechahora = NOW() -> WHERE idestu = NEW.idestu; -> END IF; -> END IF; -> END; -> // Query OK, 0 rows affected (0.101 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> MariaDB [matriculauni]> delete from matricula; Query OK, 6 rows affected (0.071 sec) MariaDB [matriculauni]> delete from pagomatricula; Query OK, 2 rows affected (0.096 sec) MariaDB [matriculauni]> delete from historialpago; Query OK, 1 row affected (0.044 sec) MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER actualizar_valmatricula_despues_insertar -> AFTER INSERT ON matricula -> FOR EACH ROW -> BEGIN -> DECLARE contador INT; -> -> -- Contar cu ntos registros existen en pagomatricula para el nuevo estudiante -> SELECT COUNT(*) INTO contador -> FROM pagomatricula AS pm -> INNER JOIN matricula AS ma ON pm.idestu = ma.idestu -> WHERE pm.idestu = NEW.idestu -> AND pm.codestadopago <> '002' -> AND ma.codestadomate <> '001'; -- No contar las materias de matr¡culas con estado '001' -> -> -- Si no hay registros en pagomatricula para el nuevo estudiante, insertar uno nuevo -> IF contador = 0 THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si hay registros en pagomatricula para el nuevo estudiante y el estado del codestadopago es '002', insertar un nuevo registro -> IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si el estado del codestadopago no es '002', actualizar el registro existente -> UPDATE pagomatricula -> SET valmatricula = valmatricula + (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), -> fechahora = NOW() -> WHERE idestu = NEW.idestu; -> END IF; -> END IF; -> END; -> // ERROR 1359 (HY000): Trigger 'matriculauni.actualizar_valmatricula_despues_insertar' already exists MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula AS pm INNER JOIN matricula AS ma ON pm.idestu = ma.idestu WHERE pm.idestu = NEW.idestu AND pm.codestadopago <> '002' AND ma.codestadomate <> '001'; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END IF; END | AFTER | 2024-03-11 22:50:55.47 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | procesar_pago | UPDATE | pagomatricula | BEGIN IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' THEN INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); UPDATE matricula SET codestadomate = '001' WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 20:38:27.69 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.033 sec) MariaDB [matriculauni]> drop trigger actualizar_valmatricula_despues_insertar; Query OK, 0 rows affected (0.003 sec) MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER actualizar_valmatricula_despues_insertar -> AFTER INSERT ON matricula -> FOR EACH ROW -> BEGIN -> DECLARE contador INT; -> -> -- Contar cu ntos registros existen en pagomatricula para el nuevo estudiante -> SELECT COUNT(*) INTO contador -> FROM pagomatricula AS pm -> INNER JOIN matricula AS ma ON pm.idestu = ma.idestu -> WHERE pm.idestu = NEW.idestu -> AND pm.codestadopago <> '002' -> AND ma.codestadomate <> '001'; -- No contar las materias de matr¡culas con estado '001' -> -> -- Si no hay registros en pagomatricula para el nuevo estudiante, insertar uno nuevo -> IF contador = 0 THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si hay registros en pagomatricula para el nuevo estudiante y el estado del codestadopago es '002', insertar un nuevo registro -> IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si el estado del codestadopago no es '002', actualizar el registro existente -> UPDATE pagomatricula -> SET valmatricula = valmatricula + (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), -> fechahora = NOW() -> WHERE idestu = NEW.idestu; -> END IF; -> END IF; -> END; -> // Query OK, 0 rows affected (0.104 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> MariaDB [matriculauni]> insert into matricula values('001','001','003'); Query OK, 1 row affected (0.161 sec) MariaDB [matriculauni]> insert into matricula values('001','002','003'); Query OK, 1 row affected (0.136 sec) MariaDB [matriculauni]> insert into matricula values('002','002','003'); Query OK, 1 row affected (0.031 sec) MariaDB [matriculauni]> insert into matricula values('001','003','003'); Query OK, 1 row affected (0.147 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 10 | 001 | 004 | 2024-03-11 | 1660000 | 001 | | 11 | 002 | 004 | 2024-03-11 | 360000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 2 rows in set (0.001 sec) MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula='10'; Query OK, 1 row affected (0.141 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 10 | 001 | 004 | 2024-03-11 | 1660000 | 002 | | 11 | 002 | 004 | 2024-03-11 | 360000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 2 rows in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values('001','002','003'); Query OK, 1 row affected (0.059 sec) MariaDB [matriculauni]> insert into matricula values('001','003','003'); ERROR 1242 (21000): Subquery returns more than 1 row MariaDB [matriculauni]> drop trigger actualizar_valmatricula_despues_insertar; Query OK, 0 rows affected (0.002 sec) MariaDB [matriculauni]> DELIMITER // MariaDB [matriculauni]> MariaDB [matriculauni]> CREATE TRIGGER actualizar_valmatricula_despues_insertar -> AFTER INSERT ON matricula -> FOR EACH ROW -> BEGIN -> DECLARE contador INT; -> -> -- Contar cu ntos registros existen en pagomatricula para el nuevo estudiante -> SELECT COUNT(*) INTO contador -> FROM pagomatricula AS pm -> WHERE pm.idestu = NEW.idestu; -> -> -- Si no hay registros en pagomatricula para el nuevo estudiante, insertar uno nuevo -> IF contador = 0 THEN -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si existe un registro para el estudiante en pagomatricula -> -- y su estado de pago es '002', eliminar ese registro y agregar uno nuevo -> IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN -> DELETE FROM pagomatricula WHERE idestu = NEW.idestu; -- Eliminar el registro existente -> INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) -> VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); -> ELSE -> -- Si el estado del codestadopago no es '002', actualizar el registro existente -> UPDATE pagomatricula -> SET valmatricula = valmatricula + (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), -> fechahora = NOW() -> WHERE idestu = NEW.idestu; -> END IF; -> END IF; -> END; -> // Query OK, 0 rows affected (0.096 sec) MariaDB [matriculauni]> MariaDB [matriculauni]> DELIMITER ; MariaDB [matriculauni]> MariaDB [matriculauni]> delete from matricula; Query OK, 5 rows affected (0.079 sec) MariaDB [matriculauni]> delete from pagomatricula; Query OK, 3 rows affected (0.158 sec) MariaDB [matriculauni]> delete from historialpago; Query OK, 1 row affected (0.042 sec) MariaDB [matriculauni]> insert into matricula values('001','001','003'); Query OK, 1 row affected (0.071 sec) MariaDB [matriculauni]> insert into matricula values('001','002','003'); Query OK, 1 row affected (0.256 sec) MariaDB [matriculauni]> insert into matricula values('001','003','003'); Query OK, 1 row affected (0.110 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 13 | 001 | 004 | 2024-03-11 | 1660000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.001 sec) MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula=13; Query OK, 1 row affected (0.164 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 13 | 001 | 004 | 2024-03-11 | 1660000 | 002 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values('001','003','003'); Query OK, 1 row affected (0.041 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 14 | 001 | 004 | 2024-03-11 | 700000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values('001','001','003'); Query OK, 1 row affected (0.175 sec) MariaDB [matriculauni]> insert into matricula values('001','004','003'); Query OK, 1 row affected (0.057 sec) MariaDB [matriculauni]> insert into matricula values('021','004','003'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`matriculauni`.`matricula`, CONSTRAINT `matricula_ibfk_1` FOREIGN KEY (`IdEstu`) REFERENCES `estudiante` (`idestu`) ON DELETE CASCADE ON UPDATE CASCADE) MariaDB [matriculauni]> insert into matricula values('002','004','003'); Query OK, 1 row affected (0.080 sec) MariaDB [matriculauni]> insert into matricula values('002','003','003'); Query OK, 1 row affected (0.176 sec) MariaDB [matriculauni]> insert into matricula values('001','003','003'); Query OK, 1 row affected (0.071 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 14 | 001 | 004 | 2024-03-11 | 3080000 | 001 | | 15 | 002 | 004 | 2024-03-11 | 1780000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 2 rows in set (0.005 sec) MariaDB [matriculauni]> select * from historialpago; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 13 | 001 | 004 | 2024-03-11 | 1660000 | 002 | +------------------+--------+-----------+------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula=15; Query OK, 1 row affected (0.179 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [matriculauni]> insert into matricula values('002','003','003'); Query OK, 1 row affected (0.048 sec) MariaDB [matriculauni]> insert into matricula values('002','004','003'); Query OK, 1 row affected (0.048 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 14 | 001 | 004 | 2024-03-11 | 3080000 | 001 | | 16 | 002 | 004 | 2024-03-11 | 1780000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 2 rows in set (0.000 sec) MariaDB [matriculauni]> delete from historialpago; Query OK, 2 rows affected (0.089 sec) MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula=14; Query OK, 1 row affected (0.165 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula=16; Query OK, 1 row affected (0.142 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [matriculauni]> select * from historialpago; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 14 | 001 | 004 | 2024-03-11 | 3080000 | 002 | | 16 | 002 | 004 | 2024-03-11 | 1780000 | 002 | +------------------+--------+-----------+------------+--------------+---------------+ 2 rows in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values('002','004','003'); Query OK, 1 row affected (0.259 sec) MariaDB [matriculauni]> insert into matricula values('002','003','003'); Query OK, 1 row affected (0.064 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 14 | 001 | 004 | 2024-03-11 | 3080000 | 002 | | 17 | 002 | 004 | 2024-03-11 | 1780000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 2 rows in set (0.000 sec) MariaDB [matriculauni]> insert into matricula values('001','003','003'); Query OK, 1 row affected (0.187 sec) MariaDB [matriculauni]> insert into matricula values('002','001','003'); Query OK, 1 row affected (0.166 sec) MariaDB [matriculauni]> insert into matricula values('001','001','003'); Query OK, 1 row affected (0.130 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 17 | 002 | 004 | 2024-03-11 | 2380000 | 001 | | 18 | 001 | 004 | 2024-03-11 | 1300000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 2 rows in set (0.000 sec) MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula=17; Query OK, 1 row affected (0.173 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [matriculauni]> update pagomatricula set codestadopago='002' where codpagomatricula=18; Query OK, 1 row affected (0.066 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [matriculauni]> select * from historialpago; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 14 | 001 | 004 | 2024-03-11 | 3080000 | 002 | | 16 | 002 | 004 | 2024-03-11 | 1780000 | 002 | | 17 | 002 | 004 | 2024-03-11 | 2380000 | 002 | | 18 | 001 | 004 | 2024-03-11 | 1300000 | 002 | +------------------+--------+-----------+------------+--------------+---------------+ 4 rows in set (0.000 sec) MariaDB [matriculauni]> select * from matricula; +--------+---------+---------------+ | IdEstu | CodMate | CodEstadomate | +--------+---------+---------------+ | 001 | 001 | 001 | | 001 | 002 | 001 | | 001 | 003 | 001 | | 001 | 003 | 001 | | 001 | 001 | 001 | | 001 | 004 | 001 | | 002 | 004 | 001 | | 002 | 003 | 001 | | 001 | 003 | 001 | | 002 | 003 | 001 | | 002 | 004 | 001 | | 002 | 004 | 001 | | 002 | 003 | 001 | | 001 | 003 | 001 | | 002 | 001 | 001 | | 001 | 001 | 001 | +--------+---------+---------------+ 16 rows in set (0.001 sec) MariaDB [matriculauni]> insert into matricula values('001','001','003'); Query OK, 1 row affected (0.267 sec) MariaDB [matriculauni]> select * from pagomatricula; +------------------+--------+-----------+------------+--------------+---------------+ | codpagomatricula | idestu | codtppago | fechahora | valmatricula | codestadopago | +------------------+--------+-----------+------------+--------------+---------------+ | 17 | 002 | 004 | 2024-03-11 | 2380000 | 002 | | 19 | 001 | 004 | 2024-03-11 | 600000 | 001 | +------------------+--------+-----------+------------+--------------+---------------+ 2 rows in set (0.000 sec) MariaDB [matriculauni]> show triggers; +------------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | calvalmateria | INSERT | materia | begin set new.valtotal=new.valcredito*new.cantcreditos; end | BEFORE | 2024-03-03 16:24:40.17 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | actualizar_valmatricula_despues_insertar | INSERT | matricula | BEGIN DECLARE contador INT; SELECT COUNT(*) INTO contador FROM pagomatricula AS pm WHERE pm.idestu = NEW.idestu; IF contador = 0 THEN INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE IF (SELECT codestadopago FROM pagomatricula WHERE idestu = NEW.idestu) = '002' THEN DELETE FROM pagomatricula WHERE idestu = NEW.idestu; INSERT INTO pagomatricula (idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.idestu, '004', NOW(), (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), '001'); ELSE UPDATE pagomatricula SET valmatricula = valmatricula + (SELECT SUM(m.valtotal) FROM materia AS m WHERE m.codmate = NEW.codmate), fechahora = NOW() WHERE idestu = NEW.idestu; END IF; END IF; END | AFTER | 2024-03-11 22:58:37.19 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | | procesar_pago | UPDATE | pagomatricula | BEGIN IF OLD.codestadopago != '002' AND NEW.codestadopago = '002' THEN INSERT INTO historialpago (codpagomatricula, idestu, codtppago, fechahora, valmatricula, codestadopago) VALUES (NEW.codpagomatricula, NEW.idestu, NEW.codtppago, NOW(), NEW.valmatricula, NEW.codestadopago); UPDATE matricula SET codestadomate = '001' WHERE idestu = NEW.idestu; END IF; END | AFTER | 2024-03-11 20:38:27.69 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 | cp850_general_ci | utf8mb4_general_ci | +------------------------------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.042 sec) MariaDB [matriculauni]> exit