PRCATICA 8

mysql> create database dbclientes;

Query OK, 1 row affected (0.14 sec)

mysql> use dbclientes;

Database changed

mysql> create table tbclientes(codigo_cli int primary key,nombre_cli varchar(250),trabajo_cli varchar(250));

Query OK, 0 rows affected (0.13 sec)

mysql> describe tbclientes;

+-------------+--------------+------+-----+---------+-------+

| Field       | Type         | Null | Key | Default | Extra |

+-------------+--------------+------+-----+---------+-------+

| codigo_cli  | int(11)      | NO   | PRI | NULL    |       |

| nombre_cli  | varchar(250) | YES  |     | NULL    |       |

| trabajo_cli | varchar(250) | YES  |     | NULL    |       |

+-------------+--------------+------+-----+---------+-------+

3 rows in set (0.11 sec)

mysql> insert into tbclientes(codigo_cli,nombre_cli,trabajo_cli) values(1,'Edith Martinez Hernandez','Informatica'),(2,'Juan Carmona Hernandez','Control de la Contaminacion') ,(3,'Fernanda Lopez Perez','Productividad');

Query OK, 3 rows affected (0.06 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tbclientes;

+------------+--------------------------+-----------------------------+

| codigo_cli | nombre_cli               | trabajo_cli                 |

+------------+--------------------------+-----------------------------+

|          1 | Edith Martinez Hernandez | Informatica                 |

|          2 | Juan Carmona Hernandez   | Control de la Contaminacion |

|          3 | Fernanda Lopez Perez     | Productividad               |

+------------+--------------------------+-----------------------------+

3 rows in set (0.00 sec)

mysql> CREATE TABLE tbcopiaclientes(codigo_cop int primary key,nombre_cop varcha

r(50),trabajo_cop varchar(50),usuario_cop varchar(50),modificado_cop datetime);

Query OK, 0 rows affected (0.05 sec)

mysql> describe tbclientes;

+-------------+--------------+------+-----+---------+-------+

| Field       | Type         | Null | Key | Default | Extra |

+-------------+--------------+------+-----+---------+-------+

| codigo_cli  | int(11)      | NO   | PRI | NULL    |       |

| nombre_cli  | varchar(250) | YES  |     | NULL    |       |

| trabajo_cli | varchar(250) | YES  |     | NULL    |       |

+-------------+--------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

mysql> delimiter //

mysql> create trigger nuevocliente after insert on tbclientes

    -> for each row

    -> begin

    -> insert into tbcopiaclientes(codigo_cop,nombre_cop,trabajo_cop,usuario_cop

,modificado_cop)values(new.codigo_cli,new.nombre_cli,new.trabajo_cli,CURRENT_USE

R(),now());

    -> end;

    -> //

Query OK, 0 rows affected (0.12 sec)

    -> show triggers //

+--------------+--------+------------+------------------------------------------

--------------------------------------------------------------------------------

------------------------------------------------------+--------+---------+------

--------------------------------------+----------------+

| Trigger      | Event  | Table      | Statement

 

                                                      | Timing | Created | sql_m

ode                                   | Definer        |

+--------------+--------+------------+------------------------------------------

--------------------------------------------------------------------------------

------------------------------------------------------+--------+---------+------

--------------------------------------+----------------+

| nuevocliente | INSERT | tbclientes | begin

insert into tbcopiaclientes(codigo_cop,nombre_cop,trabajo_cop,usuario_cop,modifi

cado_cop)values(new.codigo_cli,new.nombre_cli,new.trabajo_cli,CURRENT_USER(),now());

end | AFTER  | NULL    | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@local

host |

+--------------+--------+------------+------------------------------------------

--------------------------------------------------------------------------------

------------------------------------------------------+--------+---------+------

--------------------------------------+----------------+

1 row in set (0.03 sec)

Query OK, 1 row affected (0.16 sec)

+------------+--------------------------+-----------------------------+

| codigo_cli | nombre_cli               | trabajo_cli                 |

+------------+--------------------------+-----------------------------+

|          1 | Edith Martinez Hernandez | Informatica                 |

|          2 | Juan Carmona Hernandez   | Control de la Contaminacion |

|          3 | Fernanda Lopez Perez     | Productividad               |

|          4 | Crlos manuel zavala      | informatica                 |

+------------+--------------------------+-----------------------------+

4 rows in set (0.16 sec)

+------------+---------------------+-------------+----------------+---------------------+

| codigo_cop | nombre_cop          | trabajo_cop | usuario_cop    | modificado_c

op      |

+------------+---------------------+-------------+----------------+---------------------+

|          4 | Crlos manuel zavala | informatica | root@localhost | 2014-05-16 0

9:27:39 |

+------------+---------------------+-------------+----------------+---------------------+

1 row in set (0.18 sec)

+--------------+--------+------------+------------------------------------------

--------------------------------------------------------------------------------

------------------------------------------------------+--------+---------+------

--------------------------------------+----------------+

| Trigger      | Event  | Table      | Statement

 

                                                      | Timing | Created | sql_m

ode                                   | Definer        |

+--------------+--------+------------+------------------------------------------

--------------------------------------------------------------------------------

------------------------------------------------------+--------+---------+------

--------------------------------------+----------------+

| nuevocliente | INSERT | tbclientes | begin

insert into tbcopiaclientes(codigo_cop,nombre_cop,trabajo_cop,usuario_cop,modifi

cado_cop)values(new.codigo_cli,new.nombre_cli,new.trabajo_cli,CURRENT_USER(),now

());

end | AFTER  | NULL    | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@local

host |

+--------------+--------+------------+------------------------------------------

--------------------------------------------------------------------------------

------------------------------------------------------+--------+---------+------

--------------------------------------+----------------+

1 row in set (0.22 sec)

mysql> insert into tbclientes() values(4,'Carlos manuel zavala','Informatica')//

Query OK, 1 row affected (0.00 sec)

mysql> select * from tbclientes//

+------------+--------------------------+-----------------------------+

| codigo_cli | nombre_cli               | trabajo_cli                 |

+------------+--------------------------+-----------------------------+

|          1 | Edith Martinez Hernandez | Informatica                 |

|          2 | Juan Carmona Hernandez   | Control de la Contaminacion |

|          3 | Fernanda Lopez Perez     | Productividad               |

|          4 | Crlos manuel zavala      | informatica                 |

+------------+--------------------------+-----------------------------+

5 rows in set (0.00 sec)

mysql> select * from  tbcopiaclientes//

+------------+----------------------+-------------+----------------+---------------------+

| codigo_cop | nombre_cop           | trabajo_cop | usuario_cop    | modificado_cop      |

+------------+----------------------+-------------+----------------+---------------------+

|          4 | Crlos manuel zavala  | informatica | root@localhost | 2014-05-1609:27:39 |

+------------+----------------------+-------------+----------------+---------------------+

2 rows in set (0.00 sec)