DISPARADORES
TRIGGER UPDATE HOSPITALES
mysql> use ministerio_salud;
Database changed
mysql> create table copia_hospital(codigo_identificacion_ant integer(50), nombre_ant varchar(50), telefono_ant integer(50), direccion_ant varchar(50),
cantidad_de_camas_ant integer(50), usuario varchar(50), modificado datetime, proceso varchar(50));
Query OK, 0 rows affected (0.07 sec)
mysql> delimiter //
mysql> create trigger actuAlizar_copia_hospital before update on hospital for each row begin insert into copia_hospital(codigo_identificacion_ant, nom
bre_ant, telefono_ant, direccion_ant, cantidad_de_camas_ant, usuario, modificado, proceso) values (old.codigo_identificacion, old.nombre, new.codigo_i
dentificacion, new.nombre, CURRENT_USE, now());
-> end;
-> //
Query OK, 0 rows affected (0.02 sec)
mysql> show triggers//
+---------------------------+--------+----------+-----------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
---------------+--------+---------+--------------------------------------------+----------------+
| Trigger | Event | Table | Statement
| Timing | Created | sql_mode | Definer |
+---------------------------+--------+----------+-----------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
---------------+--------+---------+--------------------------------------------+----------------+
| actuAlizar_copia_hospital | UPDATE | hospital | begin insert into copia_hospital(codigo_identificacion_ant, nombre_ant, telefono_ant, direccion_ant,
cantidad_de_camas_ant, usuario, modificado, proceso) values (old.codigo_identificacion, old.nombre, new.codigo_identificacion, new.nombre, CURRENT_US
E, now());
end | BEFORE | NULL | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost |
+---------------------------+--------+----------+-----------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
---------------+--------+---------+--------------------------------------------+----------------+
1 row in set (0.21 sec)
TRIGGER UPDATE MEDICO
mysql> use ministerio_salud;
Database changed
mysql> create table copia_medico(nombre_anterior varchar(50), cedula_identificacion_anterior integer(50), nombre_nuevo varchar(50), cedula_identificac
ion_nuevo integer(50), usuario varchar(50), modificado datetime, proceso varchar (50));
Query OK, 0 rows affected (0.08 sec)
mysql> describe copia_medico;
+--------------------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------------+-------------+------+-----+---------+-------+
| nombre_anterior | varchar(50) | YES | | NULL | |
| cedula_identificacion_anterior | int(50) | YES | | NULL | |
| nombre_nuevo | varchar(50) | YES | | NULL | |
| cedula_identificacion_nuevo | int(50) | YES | | NULL | |
| usuario | varchar(50) | YES | | NULL | |
| modificado | datetime | YES | | NULL | |
| proceso | varchar(50) | YES | | NULL | |
+--------------------------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> delimiter //
mysql> create trigger modifica_copia_medico before update on medico for each row begin insert into copia_medico(nombre_anterior, cedula_identificacion
_anterior, nombre_nuevo, cedula_identificacion_nuevo, usuario, modificado) values (old.nombre, old.cedula_identificacion, new.nombre, new.cedula_ident
ificacion, CURRENT_USER(), now());
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> show triggers;
+-----------------------+--------+--------+-----------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------
+
| Trigger | Event | Table | Statement
| Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation
|
+-----------------------+--------+--------+-----------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------
+
| modifica_copia_medico | UPDATE | medico | begin insert into copia_medico(nombre_anterior, cedula_identificacion_anterior, nombre_nuevo, cedula_ident
ificacion_nuevo, usuario, modificado) values (old.nombre, old.cedula_identificacion, new.nombre, new.cedula_identificacion, CURRENT_USER(), now());
end | BEFORE | NULL | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | utf8_general_ci
|
| modifica_copia_salas | UPDATE | salas | begin insert into copia_salas(nombre_sala_anterior, codigo_sala_anterior, nombre_sala_nuevo, codigo_sala_n
uevo, usuario, modificdo) values (old.nombre_sala, old.codigo_sala, new.nombre_sala, new.codigo_sala, CURRENT_USER(), now());
end | BEFORE | NULL | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci
| utf8_general_ci |
+-----------------------+--------+--------+-----------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------
+
2 rows in set (0.02 sec)
mysql> update medico set nombre="roberto_avalos", cedula_identificacion=1234554;
ERROR 1062 (23000): Duplicate entry '1234554' for key 'PRIMARY'
mysql> select * from medico;
+--------------------------------+-----------------------+
| nombre | cedula_identificacion |
+--------------------------------+-----------------------+
| roberto_avalos | 678900 |
| juan eugenio martinez elizondo | 120061 |
| manuel sierra escobell | 120062 |
| ramiro gonzalez | 120063 |
| raul ramires | 120064 |
| roberto rodriges | 120065 |
| sergio trevi¤o | 120066 |
| ramiro novelo | 120067 |
| pablo dominguez | 120068 |
| aurora estrada | 120069 |
+--------------------------------+-----------------------+
10 rows in set (0.00 sec)
mysql> select * from copia_medico;
+--------------------------------+--------------------------------+----------------+-----------------------------+----------------+-------------------
--+---------+
| nombre_anterior | cedula_identificacion_anterior | nombre_nuevo | cedula_identificacion_nuevo | usuario | modificado
| proceso |
+--------------------------------+--------------------------------+----------------+-----------------------------+----------------+-------------------
--+---------+
| jorge ramos | 120060 | roberto_avalos | 1234554 | root@localhost | 2014-05-25 20:12:5
4 | NULL |
| juan eugenio martinez elizondo | 120061 | roberto_avalos | 1234554 | root@localhost | 2014-05-25 20:12:5
4 | NULL |
| roberto_avalos | 1234554 | roberto_avalos | 2234554 | root@localhost | 2014-05-25 20:13:2
1 | NULL |
| juan eugenio martinez elizondo | 120061 | roberto_avalos | 2234554 | root@localhost | 2014-05-25 20:13:2
1 | NULL |
| roberto_avalos | 2234554 | roberto_avalos | 0 | root@localhost | 2014-05-25 20:18:3
0 | NULL |
| juan eugenio martinez elizondo | 120061 | roberto_avalos | 0 | root@localhost | 2014-05-25 20:18:3
0 | NULL |
| roberto_avalos | 0 | roberto_avalos | 678900 | root@localhost | 2014-05-25 20:19:0
0 | NULL |
| juan eugenio martinez elizondo | 120061 | roberto_avalos | 678900 | root@localhost | 2014-05-25 20:19:0
0 | NULL |
+--------------------------------+--------------------------------+----------------+-----------------------------+----------------+-------------------
--+---------+
8 rows in set (0.00 sec)
TRIGGER UPDATE PACIENTE
mysql> use ministerio_salud;
Database changed
mysql> create table copia_pacientes(cedula_identificacion_ant integer(50), nombre_comp_ant varchar(50), fecha_de_nacimiento_ant datetime, sexo_ant var
char(50), direccion_ant varchar(50), usuario varchar(50), modificado datetime, proceso varchar(50));
Query OK, 0 rows affected (0.56 sec)
mysql> describe copia_pacientes;
+---------------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+-------------+------+-----+---------+-------+
| cedula_identificacion_ant | int(50) | YES | | NULL | |
| nombre_comp_ant | varchar(50) | YES | | NULL | |
| fecha_de_nacimiento_ant | datetime | YES | | NULL | |
| sexo_ant | varchar(50) | YES | | NULL | |
| direccion_ant | varchar(50) | YES | | NULL | |
| usuario | varchar(50) | YES | | NULL | |
| modificado | datetime | YES | | NULL | |
| proceso | varchar(50) | YES | | NULL | |
+---------------------------+-------------+------+-----+---------+-------+
8 rows in set (0.02 sec)
mysql> delimiter //
mysql> create trigger modifica_copia_clientes before update on pacientes for each row begin insert into copia_pacientes(cedula_identificacion_ant, nom
bre_comp_ant, usuario, modificado, proceso, cedula_identificacion_nue, nombre_comp_nue) values (OLD.cedula_identificacion, OLD.nombre_comp, CURRENT_US
ER(), now(), NEW.cedula_identificacion, NEW.nombre_comp);
-> end;
-> //
Query OK, 0 rows affected (0.02 sec)
mysql> show triggers;
+-------------------------+--------+-----------+------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------+--------+---------+--------------------------------------------+----------------+----------------------+-------------------
---+--------------------+
| Trigger | Event | Table | Statement
| Timing | Created | sql_mode | Definer | character_set_client | collation_connecti
on | Database Collation |
+-------------------------+--------+-----------+------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------+--------+---------+--------------------------------------------+----------------+----------------------+-------------------
---+--------------------+
| modifica_copia_clientes | UPDATE | pacientes | begin insert into copia_pacientes(cedula_identificacion_ant, nombre_comp_ant, usuario, modificado, pr
oceso, cedula_identificacion_nue, nombre_comp_nue) values (OLD.cedula_identificacion, OLD.nombre_comp, CURRENT_USER(), now(), NEW.cedula_identificacio
n, NEW.nombre_comp);
end | BEFORE | NULL | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | utf8_general_ci
|
+-------------------------+--------+-----------+------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------+--------+---------+--------------------------------------------+----------------+----------------------+-------------------
---+--------------------+
1 row in set (0.00 sec)
TRIGGER UPDATE LABORATORIOS
mysql> create table copia_laboratorios(codigo_anterior integer(50), nombre_anterior varchar(100), código_nuevo integer(50), nombre_nuevo varchar(100), usuario varchar(50), modificado datetime, proceso va
rchar(50));
Query OK, 0 rows affected (0.13 sec)
mysql> describe copia_laboratorios;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| codigo_anterior | int(50) | YES | | NULL | |
| nombre_anterior | varchar(100) | YES | | NULL | |
| usuario | varchar(50) | YES | | NULL | |
| modificado | datetime | YES | | NULL | |
| proceso | varchar(50) | YES | | NULL | |
mysql> delimiter //
mysql> create trigger modifica_copia_laboratorios before update on laboratorios for each row begin insert into copia_laboratorios(codigo_anterior, nom
bre_anterior, codigo_nuevo, nombre_nuevo, usuario, modificado) values (old.codigo, old.nombre, new.codigo, new.nombre, CURRENT_USER(), now());
-> end;
-> //
Query OK, 0 rows affected (0.00 sec).
mysql> show triggerS;
-> //
+-----------------------------+--------+--------------+-----------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------
------------+
| Trigger | Event | Table | Statement
| Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Databas
e Collation |
+-----------------------------+--------+--------------+-----------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------
------------+
| modifica_copia_laboratorios | UPDATE | laboratorios | begin insert into copia_laboratorios(codigo_anterior, nombre_anterior, codigo_nuevo, nombre_nu
evo, usuario, modificado) values (old.codigo, old.nombre, new.codigo, new.nombre, CURRENT_USER(), now());
end | BEFORE | NULL | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8
| utf8_general_ci | utf8_general_ci |
| modifica_copia_medico | UPDATE | medico | begin insert into copia_medico(nombre_anterior, cedula_identificacion_anterior, nombre_nuevo,
cedula_identificacion_nuevo, usuario, modificado) values (old.nombre, old.cedula_identificacion, new.nombre, new.cedula_identificacion, CURRENT_USER()
, now());
end | BEFORE | NULL | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | utf8_general_ci
|
| modifica_copia_salas | UPDATE | salas | begin insert into copia_salas(nombre_sala_anterior, codigo_sala_anterior, nombre_sala_nuevo, c
odigo_sala_nuevo, usuario, modificdo) values (old.nombre_sala, old.codigo_sala, new.nombre_sala, new.codigo_sala, CURRENT_USER(), now());
end | BEFORE | NULL | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci
| utf8_general_ci |
+-----------------------------+--------+--------------+-----------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------
------------+
3 rows in set (0.01 sec)
mysql> update laboratorios set codigo=2345, nombre="roberto";
-> //
ERROR 1062 (23000): Duplicate entry '2345' for key 'PRIMARY'
mysql> select * from laboratorios//
+--------+------------------------------------------+-------------------------------------+------------+
| codigo | nombre | direccion | telefono |
+--------+------------------------------------------+-------------------------------------+------------+
| 2345 | roberto | calle: sofocles No.145 | 55803270 |
| 100022 | laboratorio medico azteca | medellin No.151 colonia los rosales | 52640570 |
| 100023 | laboratorio lapi de salud integral | rosa blanco No.63 | 2147483647 |
| 100024 | laboratorio chopo | gabriela mancera No.64 | 2147483647 |
| 100025 | jenner laboratorio clinico | calz de tlapan No.464 | 2147483647 |
| 100026 | laboratori bionedico de referencia | rafael checa,san angel | 2147483647 |
| 100027 | laboratori bionedico de analisis clinico | Av.teopenzolco No.115 | 523163771 |
| 100028 | laboratorio clinico de tlapan | madero 103 tlapan mexico | 565554589 |
| 100029 | laboratorio de analisis clinico | periferico sur | 55281288 |
| 100030 | laboratorio medico hectre | Av mariano escobedo No.86 | 55454751 |
+--------+------------------------------------------+-------------------------------------+------------+
10 rows in set (0.00 sec)
mysql> select * from copia_laboratorios//
+-----------------+----------------------------+--------------+--------------+----------------+---------------------+---------+
| codigo_anterior | nombre_anterior | codigo_nuevo | nombre_nuevo | usuario | modificado | proceso |
+-----------------+----------------------------+--------------+--------------+----------------+---------------------+---------+
| 100021 | laboratorio medico polanco | 2345 | roberto | root@localhost | 2014-05-25 20:55:09 | NULL |
| 100022 | laboratorio medico azteca | 2345 | roberto | root@localhost | 2014-05-25 20:55:09 | NULL |
+-----------------+----------------------------+--------------+--------------+----------------+---------------------+---------+
2 rows in set (0.00 sec)
TRIGGER DELETE HOSPITAL
mysql> use ministerio_salud;
Database changed
mysql> create table elimina_hospital(codigo_identificacion_anterior integer(50), nombre_anterior varchar(100), usuario varchar(50), modificado datetim
e, proceso varchar(50));
Query OK, 0 rows affected (0.11 sec)
mysql> describe elimina_hospital;
+--------------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------------+--------------+------+-----+---------+-------+
| codigo_identificacion_anterior | int(50) | YES | | NULL | |
| nombre_anterior | varchar(100) | YES | | NULL | |
| usuario | varchar(50) | YES | | NULL | |
| modificado | datetime | YES | | NULL | |
| proceso | varchar(50) | YES | | NULL | |
+--------------------------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> delimiter //
mysql> create trigger elimina_elimina_hospitales after delete on hospital for each row begin insert into elimina_hospitales(codigo_identificacion_ante
ior, nombre_anterior, usuario, modificado) values (old.codigo_identificacion, old.nombre, CURRENT_USER(), now());
-> end;
-> //
Query OK, 0 rows affected (0.06 sec)
mysql> delimiter ;
mysql> show triggers;
+----------------------------+--------+----------+----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------+--------+---------+--------------------------------------------+---
-------------+
| Trigger | Event | Table | Statement
| Timing | Created | sql_mode | De
finer |
+----------------------------+--------+----------+----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------+--------+---------+--------------------------------------------+---
-------------+
| elimina_elimina_hospitales | DELETE | hospital | begin insert into elimina_hospitales(codigo_identificacion_anteior, nombre_anterior, usuario, modif
icado) values (old.codigo_identificacion, old.nombre, CURRENT_USER(), now());
end | AFTER | NULL | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost |
+----------------------------+--------+----------+----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------+--------+---------+--------------------------------------------+---
-------------+
mysql> select * from hospital//
+-----------------------+------------------------------+----------+---------------------------+-------------------+
| codigo_identificacion | nombre | telefono | direccion | cantidad_de_camas |
+-----------------------+------------------------------+----------+---------------------------+-------------------+
| 11801 | centro de salud dr. manuel b | 56436615 | Joaquin Parvade col Hogar | 20 |
| 11802 | C de Salud III | 32232696 | Calle Cuauhtemoc | 16 |
| 11832 | C de Salud la Cascada | 65265266 | col La Cascada | 18 |
| 11834 | Dr Ignacio Prieto | 25536232 | Sn Bartolo | 15 |
| 11835 | Lomas de la Era | 46538916 | Trebol y Nogales | 20 |
| 11837 | c de salud tetelpan | 97612386 | Desierto de los Leones | 10 |
| 11839 | Dr Edgar Henrriquez | 3762519 | Av Chilpan | 18 |
+-----------------------+------------------------------+----------+---------------------------+-------------------+
7 rows in set (0.00 sec)
mysql> select * from elimina_hospital//
+--------------------------------+---------------------------+----------------+---------------------+---------+
| codigo_identificacion_anterior | nombre_anterior | usuario | modificado | proceso |
+--------------------------------+---------------------------+----------------+---------------------+---------+
| 11841 | edith martinez | root@localhost | 2014-05-26 08:27:29 | NULL |
| 11840 | Centro de Salud Naucalpan | root@localhost | 2014-05-26 08:28:37 | NULL |
+--------------------------------+---------------------------+----------------+---------------------+---------+
TRIGGER DELETE LABORATORIOS
mysql> use ministerio_salud;
Database changed
mysql> create table copia_laboratorios(nombre_anterior varchar(100), direccion_anterior varchar(100), usuario varchar(50), modificado varchar(50), pro
ceso varchar(50));
Query OK, 0 rows affected (0.06 sec)
mysql> describe copia_laboratorios;
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| nombre_anterior | varchar(100) | YES | | NULL | |
| direccion_anterior | varchar(100) | YES | | NULL | |
| usuario | varchar(50) | YES | | NULL | |
| modificado | varchar(50) | YES | | NULL | |
| proceso | varchar(50) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> delimiter //
mysql> create trigger elimina_copia_laboratorios after delete on laboratorios for each row begin insert into copia_laboratorios(nombre_anterior, dire
ccion_anterior, usuario,modificado) values (old.nombre, old.direccion, CURRENT_USER(), now());
-> end;
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> show triggers;
+----------------------------+--------+--------------+------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+
----------------+
| Trigger | Event | Table | Statement
| Timing | Created | sql_mode |
Definer |
+----------------------------+--------+--------------+------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+
----------------+
| elimina_elimina_hospitales | DELETE | hospital | begin insert into elimina_hospital(codigo_identificacion_anterior, nombre_anterior, usuario, mo
dificado) values (old.codigo_identificacion, old.nombre, CURRENT_USER(), now());
end | AFTER | NULL | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost |
| elimina_copia_laboratorios | DELETE | laboratorios | begin insert into copia_laboratorios(nombre_anterior, direccion_anterior, usuario,modificado) v
alues (old.nombre, old.direccion, CURRENT_USER(), now());
end | AFTER | NULL | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost |
+----------------------------+--------+--------------+------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+
----------------+
2 rows in set (0.02 sec)
mysql> delete from laboratorios where direccion='calle: sofocles No.145';
Query OK, 1 row affected (0.04 sec)
mysql> select * from laboratorios;
+--------+------------------------------------------+-------------------------------------+------------+
| codigo | nombre | direccion | telefono |
+--------+------------------------------------------+-------------------------------------+------------+
| 100022 | laboratorio medico azteca | medellin No.151 colonia los rosales | 52640570 |
| 100023 | laboratorio lapi de salud integral | rosa blanco No.63 | 2147483647 |
| 100024 | laboratorio chopo | gabriela mancera No.64 | 2147483647 |
| 100025 | jenner laboratorio clinico | calz de tlapan No.464 | 2147483647 |
| 100026 | laboratori bionedico de referencia | rafael checa,san angel | 2147483647 |
| 100027 | laboratori bionedico de analisis clinico | Av.teopenzolco No.115 | 523163771 |
| 100028 | laboratorio clinico de tlapan | madero 103 tlapan mexico | 565554589 |
| 100029 | laboratorio de analisis clinico | periferico sur | 55281288 |
| 100030 | laboratorio medico hectre | Av mariano escobedo No.86 | 55454751 |
+--------+------------------------------------------+-------------------------------------+------------+
9 rows in set (0.00 sec)
mysql> select * from copia_laboratorios;
+----------------------------+------------------------+----------------+---------------------+---------+
| nombre_anterior | direccion_anterior | usuario | modificado | proceso |
+----------------------------+------------------------+----------------+---------------------+---------+
| laboratorio medico polanco | calle: sofocles No.145 | root@localhost | 2014-05-26 08:43:49 | NULL |
+----------------------------+------------------------+----------------+---------------------+---------+
1 row in set (0.00 sec)
TRIGGER DELETE PACIENTES
mysql> use ministerio_salud;
Database changed
mysql> create table copia_pacientes(nombre_comp_ant varchar(50), fecha_nacimiento_ant datetime, usuario varchar(50), modificado datetime, proceso varc
har(50));
Query OK, 0 rows affected (0.07 sec)
mysql> describe copia_pacientes;
+----------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| nombre_comp_ant | varchar(50) | YES | | NULL | |
| fecha_nacimiento_ant | datetime | YES | | NULL | |
| usuario | varchar(50) | YES | | NULL | |
| modificado | datetime | YES | | NULL | |
| proceso | varchar(50) | YES | | NULL | |
+----------------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> delimiter //
mysql> create trigger elimina_copia_pacientes after delete on pacientes for each row begin insert into copia_pacientes(nombre_comp_ant, fecha_nacimien
to_ant, usuario, modificado) values (old.nombre_comp, old.fecha_nacimiento, CURRENT_USER(), now());
-> end;
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> show triggers;
+----------------------------+--------+--------------+------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+
----------------+
| Trigger | Event | Table | Statement
| Timing | Created | sql_mode |
Definer |
+----------------------------+--------+--------------+------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+
----------------+
| elimina_elimina_hospitales | DELETE | hospital | begin insert into elimina_hospital(codigo_identificacion_anterior, nombre_anterior, usuario, mo
dificado) values (old.codigo_identificacion, old.nombre, CURRENT_USER(), now());
end | AFTER | NULL | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost |
| elimina_copia_laboratorios | DELETE | laboratorios | begin insert into copia_laboratorios(nombre_anterior, direccion_anterior, usuario,modificado) v
alues (old.nombre, old.direccion, CURRENT_USER(), now());
end | AFTER | NULL | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost |
| elimina_copia_pacientes | DELETE | pacientes | begin insert into copia_pacientes(nombre_comp_ant, fecha_nacimiento_ant, usuario, modificado) v
alues (old.nombre_comp, old.fecha_nacimiento, CURRENT_USER(), now());
end | AFTER | NULL | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost |
+----------------------------+--------+--------------+------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+
----------------+
3 rows in set (0.02 sec)
mysql> delete from pacientes where nombre_comp='elizabeth garcia sanchez';
Query OK, 1 row affected (0.00 sec)
mysql> select * from pacientes;
+-----------------------+----------------------------------+------------------+------+-------------+
| cedula_identificacion | nombre_comp | fecha_nacimiento | sexo | direccion |
+-----------------------+----------------------------------+------------------+------+-------------+
| 12001 | miranda ayala danna gerybeht | 1997-05-01 | F | sta cesilia |
| 12002 | alvarado longoria jennifer anaid | 1997-11-30 | F | El olivo |
| 12003 | baltazar lucas diana | 1997-11-14 | F | El paraje |
| 12004 | Dereck osvaldo zamora | 2013-08-12 | M | sta cecilia |
| 12005 | Moreno segovia ismael | 1997-07-08 | M | atizapan |
| 12006 | Garcia sanchez paola | 1973-08-09 | F | tlalpan |
| 12007 | Miranda castillo brenda | 1983-09-03 | F | sta cecilia |
| 12008 | Miranda castillo alberto | 1983-09-24 | M | sta cecilia |
| 12009 | avendaño de la rosa edzon | 1997-06-15 | M | sta cecilia |
+-----------------------+----------------------------------+------------------+------+-------------+
9 rows in set (0.00 sec)
mysql> select * from copia_pacientes;
+--------------------------+----------------------+----------------+---------------------+---------+
| nombre_comp_ant | fecha_nacimiento_ant | usuario | modificado | proceso |
+--------------------------+----------------------+----------------+---------------------+---------+
| elizabeth garcia sanchez | 1997-12-02 00:00:00 | root@localhost | 2014-05-26 08:58:56 | NULL |
+--------------------------+----------------------+----------------+---------------------+---------+
1 row in set (0.00 sec)
TRIGGER DELETE MEDICO
mysql> use ministerio_salud;
Database changed
mysql> create table copia_medico(nombre_anterior varchar(30), usuario varchar(50), modificado datetime, proceso varchar(30));
Query OK, 0 rows affected (0.07 sec)
mysql> describe copia_medico;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| nombre_anterior | varchar(30) | YES | | NULL | |
| usuario | varchar(50) | YES | | NULL | |
| modificado | datetime | YES | | NULL | |
| proceso | varchar(30) | YES | | NULL | |
+-----------------+-------------+------+-----+---------+-------+
4 rows in set (0.03 sec)
mysql> delimiter //
mysql> create trigger delete_medico after delete on medico for each row begin insert into copia_medico(nombre_anterior, usuario, modificado) values (o
ld.nombre, CURRENT_USER(), now());
-> end;
-> //
Query OK, 0 rows affected (0.02 sec)
mysql> show triggers//
+---------------------------+--------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+
| Trigger | Event | Table | Statement
| Timing | Created | sql_mode | Definer |
+---------------------------+--------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+
| actuAlizar_copia_hospital | UPDATE | hospital | begin insert into copia_hospital(codigo_identificacion_ant, nombre_ant, telefono_ant, direccion_ant, cantidad_de_camas_ant, usuario, modificado, proce
so) values (old.codigo_identificacion, old.nombre, new.codigo_identificacion, new.nombre, CURRENT_USE, now());
end | BEFORE | NULL | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost |
| delete_medico | DELETE | medico | begin insert into copia_medico(nombre_anterior, usuario, modificado) values (old.nombre, CURRENT_USER(), now());
end | AFTER | NULL | NO_AUTO_CREATE_USER,NO_ENGI
NE_SUBSTITUTION | root@localhost |
+---------------------------+--------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+
2 rows in set (0.36 sec)
mysql> delimiter ;
mysql> delete from medico where nombre='jorge ramos';
Query OK, 1 row affected (0.07 sec)
mysql> select * from medico;
+--------------------------------+-----------------------+
| nombre | cedula_identificacion |
+--------------------------------+-----------------------+
| juan eugenio martinez elizondo | 120061 |
| manuel sierra escobell | 120062 |
| ramiro gonzalez | 120063 |
| raul ramires | 120064 |
| roberto rodriges | 120065 |
| sergio trevi¤o | 120066 |
| ramiro novelo | 120067 |
| pablo dominguez | 120068 |
| aurora estrada | 120069 |
+--------------------------------+-----------------------+
9 rows in set (0.00 sec)
mysql> select * from copia_medico;
+-----------------+----------------+---------------------+---------+
| nombre_anterior | usuario | modificado | proceso |
+-----------------+----------------+---------------------+---------+
| jorge ramos | root@localhost | 2014-05-29 13:22:00 | NULL |
+-----------------+----------------+---------------------+---------+
1 row in set (0.00 sec)
No hay comentarios.:
Publicar un comentario