Trigger SQL

[thebus4k]

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.

[EdePC]

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 (sql) [Seleccionar] Expandir

/*
USE master
DROP DATABASE db_prueba
*/

CREATE DATABASE db_prueba
GO
USE db_prueba

CREATE TABLE tb_persona (
  dni    CHAR(9),
  nombre VARCHAR(50)
)

INSERT INTO tb_persona VALUES
( '11111111X', 'jaimico' )

CREATE TABLE tb_logs (
  accion      CHAR(6),
  nuevo_valor CHAR(9),
  viejo_valor CHAR(9)
)

GO
CREATE TRIGGER tr_persona_insert ON tb_persona
AFTER INSERT AS
INSERT INTO tb_logs VALUES
( 'insert', (SELECT dni FROM INSERTED), NULL )

GO
CREATE TRIGGER tr_persona_delete ON tb_persona
AFTER DELETE AS
INSERT INTO tb_logs VALUES
( 'delete', NULL, (SELECT dni FROM DELETED) )

GO
CREATE TRIGGER tr_persona_update ON tb_persona
AFTER UPDATE AS
INSERT INTO tb_logs VALUES
( 'update', (SELECT dni FROM INSERTED), (SELECT dni FROM DELETED) )

///////////////////////////////////
INSERT INTO tb_persona VALUES
('33333333Z', 'paco')
SELECT * FROM tb_logs

DELETE FROM tb_persona
WHERE dni = '11111111X'
SELECT * FROM tb_logs

UPDATE tb_persona
SET dni = '22222222H'
WHERE dni = '33333333Z'
SELECT * FROM tb_logs

Mis resultados:

Código [Seleccionar] Expandir

+--------+-------------+-------------+
| 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:

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

[thebus4k]

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 (sql) [Seleccionar] Expandir

/*
USE master
DROP DATABASE db_prueba
*/

CREATE DATABASE db_prueba
GO
USE db_prueba

CREATE TABLE tb_persona (
  dni    CHAR(9),
  nombre VARCHAR(50)
)

INSERT INTO tb_persona VALUES
( '11111111X', 'jaimico' )

CREATE TABLE tb_logs (
  accion      CHAR(6),
  nuevo_valor CHAR(9),
  viejo_valor CHAR(9)
)

GO
CREATE TRIGGER tr_persona_insert ON tb_persona
AFTER INSERT AS
INSERT INTO tb_logs VALUES
( 'insert', (SELECT dni FROM INSERTED), NULL )

GO
CREATE TRIGGER tr_persona_delete ON tb_persona
AFTER DELETE AS
INSERT INTO tb_logs VALUES
( 'delete', NULL, (SELECT dni FROM DELETED) )

GO
CREATE TRIGGER tr_persona_update ON tb_persona
AFTER UPDATE AS
INSERT INTO tb_logs VALUES
( 'update', (SELECT dni FROM INSERTED), (SELECT dni FROM DELETED) )

///////////////////////////////////
INSERT INTO tb_persona VALUES
('33333333Z', 'paco')
SELECT * FROM tb_logs

DELETE FROM tb_persona
WHERE dni = '11111111X'
SELECT * FROM tb_logs

UPDATE tb_persona
SET dni = '22222222H'
WHERE dni = '33333333Z'
SELECT * FROM tb_logs

Mis resultados:

Código [Seleccionar] Expandir

+--------+-------------+-------------+
| 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

[thebus4k]

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 (sql) [Seleccionar] Expandir

/*
USE master
DROP DATABASE db_prueba
*/

CREATE DATABASE db_prueba
GO
USE db_prueba

CREATE TABLE tb_persona (
  dni    CHAR(9),
  nombre VARCHAR(50)
)

INSERT INTO tb_persona VALUES
( '11111111X', 'jaimico' )

CREATE TABLE tb_logs (
  accion      CHAR(6),
  nuevo_valor CHAR(9),
  viejo_valor CHAR(9)
)

GO
CREATE TRIGGER tr_persona_insert ON tb_persona
AFTER INSERT AS
INSERT INTO tb_logs VALUES
( 'insert', (SELECT dni FROM INSERTED), NULL )

GO
CREATE TRIGGER tr_persona_delete ON tb_persona
AFTER DELETE AS
INSERT INTO tb_logs VALUES
( 'delete', NULL, (SELECT dni FROM DELETED) )

GO
CREATE TRIGGER tr_persona_update ON tb_persona
AFTER UPDATE AS
INSERT INTO tb_logs VALUES
( 'update', (SELECT dni FROM INSERTED), (SELECT dni FROM DELETED) )

///////////////////////////////////
INSERT INTO tb_persona VALUES
('33333333Z', 'paco')
SELECT * FROM tb_logs

DELETE FROM tb_persona
WHERE dni = '11111111X'
SELECT * FROM tb_logs

UPDATE tb_persona
SET dni = '22222222H'
WHERE dni = '33333333Z'
SELECT * FROM tb_logs

Mis resultados:

Código [Seleccionar] Expandir

+--------+-------------+-------------+
| 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.

[EdePC]

- 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...

[thebus4k]

- 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

[K-YreX]

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 (sql) [Seleccionar] Expandir


CREATE TRIGGER tr_persona_insert AFTER INSERT ON tb_persona FOR EACH ROW
  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.

[thebus4k]

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 (sql) [Seleccionar] Expandir


CREATE TRIGGER tr_persona_insert AFTER INSERT ON tb_persona FOR EACH ROW
  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.

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?

[K-YreX]

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.

[thebus4k]

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.

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;$$