Foro de elhacker.net

Programación => Bases de Datos => Mensaje iniciado por: thebus4k en 29 Mayo 2020, 00:45



Título: Trigger SQL
Publicado por: thebus4k en 29 Mayo 2020, 00:45
Buenas a todos, tengo un problema con unos Trigger en sql partiendo de dos tablas dadas.
Adjunto el enunciado:

Dada una tabla Persona, con dos campos, DNI y Nombre donde DNI es la clave. Emplear  triggers que insertarán información en una tabla llamada Logs, compuesta por los campos Accion (INSERT, UPDATE o DELETE), Nuevo_Valor (que será equivalente al campo DNI de persona, pero, sin estar relacionado con esa tabla), y Viejo_Valor (igual que Nuevo_Valor)

- trigger que se ejecute despues de que se realice una inserción en Persona,  inserte en la tabla Logs la acción realizada y el DNI introducido a la tabla. Por ejemplo si se realiza un insert en persona con  DNI 33333333Z y el nombre Paco, el trigger almacenará en la tabla Logs la siguiente información ("INSERT", "33333333W", NULL)

-trigger que se ejecute despues de realizar un borrado de la tabla persona, de manera muy parecido al anterior, si se borra el usuario con DNI 11111111X, el trigger deberá almacenar en logs la siguiente información ("DELETE", NULL, "11111111X")

-Un trigger que se ejecute despues de realizar una actualización de la tabla persona, de tal manera que si se modifica el DNI de una persona por ejemplo de tener DNI 33333333Z a tener DNI 22222222H, el trigger deberá almacenar la siguiente información en la tabla Logs: ("UPDATE", "33333333Z", "2222222H")

Los que son los trigger  entiendo que funcion realizan y los tipos que existen, pero a la hora de realizar lo que pide el enunciado no se muy bien como expresarlo y que seleccionar y si aparte del trigger hay que usar algo más.

Espero que alguien pueda ayudarme con ello.

Un saludo.


Título: Re: Trigger SQL
Publicado por: EdePC en 29 Mayo 2020, 02:03
Saludos,

- Veo unas discrepancias entre tu modelo de datos, el enunciado y tus ejemplos ... Por ejemplo si yo inserto '33333333Z' no es lógico que se guarde '33333333W' como nuevo_valor XD. Por otro lado si actualizo '33333333Z' a '22222222H', lo lógico que se guarde como nuevo _valor '33333333Z' XD

- Voy a considerar lo que yo considero lógico, luego puedes hacer las correcciones que veas convenientes. Además voy a suponer que estás utilizando Microsoft SQL Server, si no es el caso puedes avisar.

Código
  1. /*
  2. USE master
  3. DROP DATABASE db_prueba
  4. */
  5.  
  6. CREATE DATABASE db_prueba
  7. GO
  8. USE db_prueba
  9.  
  10. CREATE TABLE tb_persona (
  11.  dni    CHAR(9),
  12.  nombre VARCHAR(50)
  13. )
  14.  
  15. INSERT INTO tb_persona VALUES
  16. ( '11111111X', 'jaimico' )
  17.  
  18. CREATE TABLE tb_logs (
  19.  accion      CHAR(6),
  20.  nuevo_valor CHAR(9),
  21.  viejo_valor CHAR(9)
  22. )
  23.  
  24. GO
  25. CREATE TRIGGER tr_persona_insert ON tb_persona
  26. AFTER INSERT AS
  27. INSERT INTO tb_logs VALUES
  28. ( 'insert', (SELECT dni FROM INSERTED), NULL )
  29.  
  30. GO
  31. CREATE TRIGGER tr_persona_delete ON tb_persona
  32. AFTER DELETE AS
  33. INSERT INTO tb_logs VALUES
  34. ( 'delete', NULL, (SELECT dni FROM DELETED) )
  35.  
  36. GO
  37. CREATE TRIGGER tr_persona_update ON tb_persona
  38. AFTER UPDATE AS
  39. INSERT INTO tb_logs VALUES
  40. ( 'update', (SELECT dni FROM INSERTED), (SELECT dni FROM DELETED) )
  41.  
  42. ///////////////////////////////////
  43. INSERT INTO tb_persona VALUES
  44. ('33333333Z', 'paco')
  45. SELECT * FROM tb_logs
  46.  
  47. DELETE FROM tb_persona
  48. WHERE dni = '11111111X'
  49. SELECT * FROM tb_logs
  50.  
  51. UPDATE tb_persona
  52. SET dni = '22222222H'
  53. WHERE dni = '33333333Z'
  54. SELECT * FROM tb_logs

Mis resultados:
Código:
+--------+-------------+-------------+
| accion | nuevo_valor | viejo_valor |
+--------+-------------+-------------+
| insert | 33333333Z   | NULL        |
+--------+-------------+-------------+
| delete | NULL        | 11111111X   |
+--------+-------------+-------------+
| update | 22222222H   | 33333333Z   |
+--------+-------------+-------------+

- En SQL Server exiten las tablas predeterminadas INSERTED y DELETED que solo existen para los TRIGGER, dependiendo de la Acción realizada estos contienen los campos Insertados, Actualizados o Eliminados:

Citar
+--------+-----------------------------------+-----------------------------------+
| Acción | INSERTED                          | DELETED                           |
+--------+-----------------------------------+-----------------------------------+
| INSERT | Filas insertadas                  | NULL                              |
+--------+-----------------------------------+-----------------------------------+
| UPDATE | Filas despues de ser actualizadas | Filas antes de ser actualizadas   |
+--------+-----------------------------------+-----------------------------------+
| DELETE | NULL                              | Filas eliminadas                  |
+--------+-----------------------------------+-----------------------------------+


Título: Re: Trigger SQL
Publicado por: thebus4k en 29 Mayo 2020, 02:11
Saludos,

- Veo unas discrepancias entre tu modelo de datos, el enunciado y tus ejemplos ... Por ejemplo si yo inserto '33333333Z' no es lógico que se guarde '33333333W' como nuevo_valor XD. Por otro lado si actualizo '33333333Z' a '22222222H', lo lógico que se guarde como nuevo _valor '33333333Z' XD

- Voy a considerar lo que yo considero lógico, luego puedes hacer las correcciones que veas convenientes. Además voy a suponer que estás utilizando Microsoft SQL Server, si no es el caso puedes avisar.

Código
  1. /*
  2. USE master
  3. DROP DATABASE db_prueba
  4. */
  5.  
  6. CREATE DATABASE db_prueba
  7. GO
  8. USE db_prueba
  9.  
  10. CREATE TABLE tb_persona (
  11.  dni    CHAR(9),
  12.  nombre VARCHAR(50)
  13. )
  14.  
  15. INSERT INTO tb_persona VALUES
  16. ( '11111111X', 'jaimico' )
  17.  
  18. CREATE TABLE tb_logs (
  19.  accion      CHAR(6),
  20.  nuevo_valor CHAR(9),
  21.  viejo_valor CHAR(9)
  22. )
  23.  
  24. GO
  25. CREATE TRIGGER tr_persona_insert ON tb_persona
  26. AFTER INSERT AS
  27. INSERT INTO tb_logs VALUES
  28. ( 'insert', (SELECT dni FROM INSERTED), NULL )
  29.  
  30. GO
  31. CREATE TRIGGER tr_persona_delete ON tb_persona
  32. AFTER DELETE AS
  33. INSERT INTO tb_logs VALUES
  34. ( 'delete', NULL, (SELECT dni FROM DELETED) )
  35.  
  36. GO
  37. CREATE TRIGGER tr_persona_update ON tb_persona
  38. AFTER UPDATE AS
  39. INSERT INTO tb_logs VALUES
  40. ( 'update', (SELECT dni FROM INSERTED), (SELECT dni FROM DELETED) )
  41.  
  42. ///////////////////////////////////
  43. INSERT INTO tb_persona VALUES
  44. ('33333333Z', 'paco')
  45. SELECT * FROM tb_logs
  46.  
  47. DELETE FROM tb_persona
  48. WHERE dni = '11111111X'
  49. SELECT * FROM tb_logs
  50.  
  51. UPDATE tb_persona
  52. SET dni = '22222222H'
  53. WHERE dni = '33333333Z'
  54. SELECT * FROM tb_logs

Mis resultados:
Código:
+--------+-------------+-------------+
| accion | nuevo_valor | viejo_valor |
+--------+-------------+-------------+
| insert | 33333333Z   | NULL        |
+--------+-------------+-------------+
| delete | NULL        | 11111111X   |
+--------+-------------+-------------+
| update | 22222222H   | 33333333Z   |
+--------+-------------+-------------+

- En SQL Server exiten las tablas predeterminadas INSERTED y DELETED que solo existen para los TRIGGER, dependiendo de la Acción realizada estos contienen los campos Insertados, Actualizados o Eliminados:

Hola!, muchas gracias por responder y también por indicar los errores en el enunciado del ejercicio, ha sido un error al copiar el enunciado ya que estoy trabajando a parte con otro ejercicio y he mezclado los datos
Un saludo


Título: Re: Trigger SQL
Publicado por: thebus4k en 29 Mayo 2020, 11:40
Saludos,

- Veo unas discrepancias entre tu modelo de datos, el enunciado y tus ejemplos ... Por ejemplo si yo inserto '33333333Z' no es lógico que se guarde '33333333W' como nuevo_valor XD. Por otro lado si actualizo '33333333Z' a '22222222H', lo lógico que se guarde como nuevo _valor '33333333Z' XD

- Voy a considerar lo que yo considero lógico, luego puedes hacer las correcciones que veas convenientes. Además voy a suponer que estás utilizando Microsoft SQL Server, si no es el caso puedes avisar.

Código
  1. /*
  2. USE master
  3. DROP DATABASE db_prueba
  4. */
  5.  
  6. CREATE DATABASE db_prueba
  7. GO
  8. USE db_prueba
  9.  
  10. CREATE TABLE tb_persona (
  11.  dni    CHAR(9),
  12.  nombre VARCHAR(50)
  13. )
  14.  
  15. INSERT INTO tb_persona VALUES
  16. ( '11111111X', 'jaimico' )
  17.  
  18. CREATE TABLE tb_logs (
  19.  accion      CHAR(6),
  20.  nuevo_valor CHAR(9),
  21.  viejo_valor CHAR(9)
  22. )
  23.  
  24. GO
  25. CREATE TRIGGER tr_persona_insert ON tb_persona
  26. AFTER INSERT AS
  27. INSERT INTO tb_logs VALUES
  28. ( 'insert', (SELECT dni FROM INSERTED), NULL )
  29.  
  30. GO
  31. CREATE TRIGGER tr_persona_delete ON tb_persona
  32. AFTER DELETE AS
  33. INSERT INTO tb_logs VALUES
  34. ( 'delete', NULL, (SELECT dni FROM DELETED) )
  35.  
  36. GO
  37. CREATE TRIGGER tr_persona_update ON tb_persona
  38. AFTER UPDATE AS
  39. INSERT INTO tb_logs VALUES
  40. ( 'update', (SELECT dni FROM INSERTED), (SELECT dni FROM DELETED) )
  41.  
  42. ///////////////////////////////////
  43. INSERT INTO tb_persona VALUES
  44. ('33333333Z', 'paco')
  45. SELECT * FROM tb_logs
  46.  
  47. DELETE FROM tb_persona
  48. WHERE dni = '11111111X'
  49. SELECT * FROM tb_logs
  50.  
  51. UPDATE tb_persona
  52. SET dni = '22222222H'
  53. WHERE dni = '33333333Z'
  54. SELECT * FROM tb_logs

Mis resultados:
Código:
+--------+-------------+-------------+
| accion | nuevo_valor | viejo_valor |
+--------+-------------+-------------+
| insert | 33333333Z   | NULL        |
+--------+-------------+-------------+
| delete | NULL        | 11111111X   |
+--------+-------------+-------------+
| update | 22222222H   | 33333333Z   |
+--------+-------------+-------------+

- En SQL Server exiten las tablas predeterminadas INSERTED y DELETED que solo existen para los TRIGGER, dependiendo de la Acción realizada estos contienen los campos Insertados, Actualizados o Eliminados:

Hola de nuevo, estoy usando tu código y me genera errores en todo el código.
Estoy usando HeidiSql.


Título: Re: Trigger SQL
Publicado por: EdePC en 29 Mayo 2020, 14:33
- La sintaxis varía dependiendo del Motor de Base de Datos, en mi caso utilicé SQL Server. ¿Qué Motor estás utilizando? => SQL Server, MySQL, PosgreSQL, SQLite, Oracle, etc, etc...


Título: Re: Trigger SQL
Publicado por: thebus4k en 29 Mayo 2020, 14:39
- La sintaxis varía dependiendo del Motor de Base de Datos, en mi caso utilicé SQL Server. ¿Qué Motor estás utilizando? => SQL Server, MySQL, PosgreSQL, SQLite, Oracle, etc, etc...
MariaDB que creo que es MySQL


Título: Re: Trigger SQL
Publicado por: K-YreX en 29 Mayo 2020, 15:47
MariaDB que creo que es MySQL

En ese caso no puedes usar las tablas temporales INSERTED y DELETED pues estas son propias de SQL Server.
El símil que existe en MySQL son las partículas NEW y OLD antes del nombre de una columna.

El primer trigger quedaría:
Código
  1. CREATE TRIGGER tr_persona_insert AFTER INSERT ON tb_persona FOR EACH ROW
  2.  INSERT INTO tb_logs VALUES ('insert', NEW.dni, NULL);
La claúsula FOR EACH ROW sirve para los casos en los que insertas varios registros con una única sentencia INSERT. Así el trigger se ejecutará una vez por cada registro. El opuesto sería FOR EACH STATEMENT.

El siguiente trigger no tiene ninguna complicación. Tendrás que usar OLD en vez de NEW.

Para el tercer trigger, el de UPDATE, tendrás que usar BEFORE en vez de AFTER para poder acceder tanto al valor nuevo (NEW) como al viejo (OLD).

Inténtalo y comenta si tienes algún problema.
Suerte. :-X


Título: Re: Trigger SQL
Publicado por: thebus4k en 29 Mayo 2020, 15:57
En ese caso no puedes usar las tablas temporales INSERTED y DELETED pues estas son propias de SQL Server.
El símil que existe en MySQL son las partículas NEW y OLD antes del nombre de una columna.

El primer trigger quedaría:
Código
  1. CREATE TRIGGER tr_persona_insert AFTER INSERT ON tb_persona FOR EACH ROW
  2.  INSERT INTO tb_logs VALUES ('insert', NEW.dni, NULL);
La claúsula FOR EACH ROW sirve para los casos en los que insertas varios registros con una única sentencia INSERT. Así el trigger se ejecutará una vez por cada registro. El opuesto sería FOR EACH STATEMENT.

El siguiente trigger no tiene ninguna complicación. Tendrás que usar OLD en vez de NEW.

Para el tercer trigger, el de UPDATE, tendrás que usar BEFORE en vez de AFTER para poder acceder tanto al valor nuevo (NEW) como al viejo (OLD).

Inténtalo y comenta si tienes algún problema.
Suerte. :-X
Hola, gracias por responder, voy a probar y te comento.
El segundo según lo que me comentas sería sustituir new por old, y también habría que sustituir Insert on por delete on?
Y en el tercero al igual que en el segundo habría que sustituir algún valor más?


Título: Re: Trigger SQL
Publicado por: K-YreX en 29 Mayo 2020, 16:09
Hola, gracias por responder, voy a probar y te comento.
El segundo según lo que me comentas sería sustituir new por old, y también habría que sustituir Insert on por delete on?
Y en el tercero al igual que en el segundo habría que sustituir algún valor más?
Claro, exactamente. En el segundo caso sería AFTER DELETE ON.

Y en el tercer caso sustituir el INSERT/DELETE por UPDATE. Es más, te diría que igual se puede (y debería ser) AFTER UPDATE. Porque en el caso de que no se confirme la actualización, no quieres que se guarde ese "intento de cambio".
No puedo confirmártelo, lo siento, pero haz ambas pruebas y una de las dos tiene que funcionar seguro.

Suerte. :-X


Título: Re: Trigger SQL
Publicado por: thebus4k en 29 Mayo 2020, 16:48
Claro, exactamente. En el segundo caso sería AFTER DELETE ON.

Y en el tercer caso sustituir el INSERT/DELETE por UPDATE. Es más, te diría que igual se puede (y debería ser) AFTER UPDATE. Porque en el caso de que no se confirme la actualización, no quieres que se guarde ese "intento de cambio".
No puedo confirmártelo, lo siento, pero haz ambas pruebas y una de las dos tiene que funcionar seguro.

Suerte. :-X
Ya lo tengo, al final me ha quedado así, no entiendo muy bien el por qué del  DELIMITER $$ etc pero nos lo exigen así.

DELIMITER $$
CREATE TRIGGER tr_persona_insert
AFTER INSERT ON persona
FOR EACH ROW
BEGIN
INSERT INTO logs1
VALUES ('insert', NEW.dni, NULL)
END;$$

DELIMITER $$
CREATE TRIGGER tr_persona_delete
AFTER DELETE ON persona
FOR EACH ROW
BEGIN
INSERT INTO logs1
VALUES ('insert', OLD.dni, NULL)
END;$$

DELIMITER $$
CREATE TRIGGER tr_persona_update
AFTER UPDATE ON persona
FOR EACH ROW
BEGIN
INSERT INTO logs1
VALUES ('update', OLD.dni, NEW.dni)
END;$$


Título: Re: Trigger SQL
Publicado por: K-YreX en 29 Mayo 2020, 16:56
Ya lo tengo, al final me ha quedado así, no entiendo muy bien el por qué del  DELIMITER $$ etc pero nos lo exigen así.
En MySQL es obligatorio que cada sentencia termine con punto y coma a diferencia de SQL Server por ejemplo. Si un trigger tiene varias instrucciones, cada una tiene que terminar en punto y coma. Entonces para delimitar el trigger completo tienes que usar un delimitador diferente de ahí el DELIMITER $$.

En estos casos como el trigger solo tiene una instrucción, puedes obviar el BEGIN...END y así no necesitas usar DELIMITER. Pero para otros casos en los que tus triggers tengan más de una instrucción, estarás obligado a utilizarlo.

PD: Utiliza las etiquetas de Código GeSHi para mostrar código. En tu caso selecciona la correspondiente a SQL.


Título: Re: Trigger SQL
Publicado por: thebus4k en 29 Mayo 2020, 17:12
En MySQL es obligatorio que cada sentencia termine con punto y coma a diferencia de SQL Server por ejemplo. Si un trigger tiene varias instrucciones, cada una tiene que terminar en punto y coma. Entonces para delimitar el trigger completo tienes que usar un delimitador diferente de ahí el DELIMITER $$.

En estos casos como el trigger solo tiene una instrucción, puedes obviar el BEGIN...END y así no necesitas usar DELIMITER. Pero para otros casos en los que tus triggers tengan más de una instrucción, estarás obligado a utilizarlo.

PD: Utiliza las etiquetas de Código GeSHi para mostrar código. En tu caso selecciona la correspondiente a SQL.
De acuerdo, para la próxima me lo apunto.
Gracias y un saludo. ;)