Auditoría MySQL

[REHome]

Estoy usando el MySQL 5.1.34. He creado una base de datos y tablas como muestra el código abajo.

Código:

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Servidor: localhost
-- Tiempo de generación: 03-07-2009 a las 20:43:33
-- Versión del servidor: 5.1.34
-- Versión de PHP: 5.2.10

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Base de datos: `prueba01`
--

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

--
-- Estructura de tabla para la tabla `datos`
--

CREATE TABLE IF NOT EXISTS `datos` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `RELE_1` tinyint(4) NOT NULL,
  `RELE_2` tinyint(4) NOT NULL,
  `RELE_3` tinyint(4) NOT NULL,
  `RELE_4` tinyint(4) NOT NULL,
  `FECHA_HORA` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci AUTO_INCREMENT=3 ;

--
-- Volcar la base de datos para la tabla `datos`
--

INSERT INTO `datos` (`ID`, `RELE_1`, `RELE_2`, `RELE_3`, `RELE_4`, `FECHA_HORA`) VALUES
(1, 1, 0, 1, 0, '2009-07-03 15:01:37'),
(2, 1, 0, 1, 1, '2009-07-03 15:21:48');

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

--
-- Estructura de tabla para la tabla `dispositivos`
--

CREATE TABLE IF NOT EXISTS `dispositivos` (
  `ID` int(11) NOT NULL,
  `nombre` varchar(30) NOT NULL,
  `estado` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `descripcion` varchar(200) NOT NULL,
  PRIMARY KEY (`ID`),
  FULLTEXT KEY `nombre` (`nombre`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Volcar la base de datos para la tabla `dispositivos`
--

INSERT INTO `dispositivos` (`ID`, `nombre`, `estado`, `descripcion`) VALUES
(1, 'RELE 1', 0, 'Motor 1'),
(2, 'RELE 2', 0, 'Motor 2'),
(3, 'RELE 3', 0, 'Alarma'),
(4, 'RELE 4', 0, 'WebCam');

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

--
-- Estructura de tabla para la tabla `tabla01`
--

CREATE TABLE IF NOT EXISTS `tabla01` (
  `RELE_1` tinyint(1) DEFAULT NULL,
  `RELE_2` tinyint(1) DEFAULT NULL,
  `RELE_3` tinyint(1) DEFAULT NULL,
  `RELE_4` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

--
-- Volcar la base de datos para la tabla `tabla01`
--

INSERT INTO `tabla01` (`RELE_1`, `RELE_2`, `RELE_3`, `RELE_4`) VALUES
(1, 1, 0, 0);

Quiero aplicar este código en phpmyadmin o cual sea, este código que me da error.

Código:

CREATE TRIGGER datos AFTER UPDATE ON dispositivos 
   FOR EACH ROW
   BEGIN
   INSERT INTO datos(nombre, estado, descripcion)
   VALUES (NEW.rele_1, NEW.rele_2, NEW.rele_3, NEW.rele_4, NOW() );
   END;

Concretamente me da error en esta línea.

Código:

   VALUES (NEW.rele_1, NEW.rele_2, NEW.rele_3, NEW.rele_4, NOW() );

[huesos52]

INSERT INTO datos(nombre, estado, descripcion)

Le estas diciendo que entre 3 valores y le estas enviando 5.

VALUES (NEW.rele_1, NEW.rele_2, NEW.rele_3, NEW.rele_4, NOW() );

Debes corregir eso y de seguro te funciona.

Un saludo.

[REHome]

Hola:

Lo he intentado mejorar:

Código:

CREATE TRIGGER trigger_datos AFTER UPDATE ON dispositivos
   FOR EACH ROW
   BEGIN
   INSERT INTO datos(ID, RELE_1, RELE_2, RELE_3, RELE_4, FECHA_HORA)
   VALUES (OLD.RELE_1, OLD.RELE_2, OLD.RELE_3, OLD.RELE_4, NOW() );
   END;

Me da fallo.

[huesos52]

Estas haciendo lo mismo.
Le dices que reciba 6 parametros, y le envias unicamente 5.
Mandale el ID también en el values.

[REHome]

Es verdad:

Código:

CREATE TRIGGER trigger_datos AFTER UPDATE ON dispositivos
   FOR EACH ROW
   BEGIN
   INSERT INTO datos(RELE_1, RELE_2, RELE_3, RELE_4, FECHA_HORA)
   VALUES (OLD.RELE_1, OLD.RELE_2, OLD.RELE_3, OLD.RELE_4, NOW() );
   END;

Sigue con el mismo fallo.

Debe quedar reflejado estos valores si cambia cualquer valor 1 ó 0.

[huesos52]

CREATE TABLE IF NOT EXISTS `dispositivos` (
`ID` int(11) NOT NULL,
`nombre` varchar(30) NOT NULL,
`estado` tinyint(3) unsigned NOT NULL DEFAULT '0',
`descripcion` varchar(200) NOT NULL,
PRIMARY KEY (`ID`),
FULLTEXT KEY `nombre` (`nombre`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Si esta es tu tabla.. de donde salen los valores old.rel_1,old.rel_2,old.rel_3 y old.rel_4?

Recuerda que al utilizar las propiedades old y new, se hace referencia a los campos de la tabla en que se dispara el trigger, para este caso dispositivos.
Mira que la tabla datos, no tiene esos campos, sino nombre, estado descripcion etc.

Realmente... que necesitas hacer?

[REHome]

Menudo lío mental tengo conmigo mismo, aún así gracias por la ayuda.

En la imagen de abajo, donde pone estado, se puede modificar 0 ó 1. Cualquier estado de cualquier orden de los RELE_1 al 4 que en este caso es el 1,1,1,1.



En esta otra imagen justo abajo en la tabla llamada datos. Se vayan actualizando los datos de 0 y 1 junto con la fecha/hora. Olvida el campo COMENTARIOS.



Eso es lo que deseo hacer. Espero que esta vez me haya explciado mejor.

[huesos52]

La tabla dispositivos solo tendrá 4 registros?
Lo que tratas de hacer, no se puede hacer directamente y no tiene mucho sentiddo.
Le encontraría sentido (pero no estaria deacuerdo) si la tabla dispositivos solo tuviera 4 registros para siempre.

tendrías que insertar en la tabla el nombre tomado de un slect.

es así?

[REHome]

Tiene sentido, lo que no me he explicado bien.

Tengo un programa medio hecho con Visual C#.



En el campo estado donde pone 1, 0, 0, 0 se actualiza solo con 1 ó 0. En los botones de al lado lo hacen. Esta interfaz está conectada a MySQL. Ahora, lo que quiero es una auditoría sobre el cambio de esos estados binarios con su fecha/hora en el momento que cambia de estado.

También se puede hacer con sólo actualizarlo desde phpmyadmin.

INSERT INTO `dispositivos` (`ID`, `nombre`, `estado`, `descripcion`) VALUES
(1, 'RELE 1', 1, 'Motor 1'),
(2, 'RELE 2', 0, 'Motor 2'),
(3, 'RELE 3', 1, 'Alarma'),
(4, 'RELE 4', 0, 'WebCam');

Hay un aexplicación aquí que funciona el ejemplo que usa, pero no consigo hacerlo de mi manera.

http://www.mysql-hispano.org/page.php?id=36&pag=1

Con cada estado se va agregando ID nueva, yn ejemplo es este de abajo:


Ignora el campo COMENTARIO, aunque lo veas en la imagen,en realidad lo he borrado.

En la fila ID=1, su estado del RELE_1 al RELE_4 es:

1,0,1,0

que al cambiar sólo el RELE_4 (que puede ser cualquiera), es decir, que como el actual es 0 lo actualizamos a 1 y su resultado es:

1,0,1,1

igual que la imagen de arriba para el ID=2.

Si ahora cambia cualquier estado, se crea un nuevo ID llamdo 3 y guarda los datos, incluido fecha/hora.

[huesos52]

El ejemplo es totalmente diferente.
Realiza el registro con los dos campos involucrados e inserta variables del sistema como current_user() y now() por eo funciona. ademas ñlo hace registro por registro y no como buscas hacerlo.. cada cuatro registros.

segun te entiendo... el primer insert, debe salvar un valor, el segundo va para el campo 2, el tercer insert va para el campo 3 y asi´sucesivamente.
El problema es que el trigger se dispara ante un cambio en dispositivos y no esperar 4 cambios para poder actualizar.

O no estoy entendiendo?

[REHome]

Bueno, veo que lo estoy haciendo mal.

¿Es mejor tener la tabla principal algo similar a esta?





Me olvidé ponerle el ID al principio y fehca/hora al final, ejejje. Para luego guardar los estados en la tabla datos.

NOTA: En la imagen se llama tabla01, en realidad es dispositivos.

Código:

--
-- Base de datos: `prueba01`
--

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

--
-- Estructura de tabla para la tabla `tabla01`
--

CREATE TABLE IF NOT EXISTS `tabla01` (
  `RELE_1` tinyint(1) DEFAULT NULL,
  `RELE_2` tinyint(1) DEFAULT NULL,
  `RELE_3` tinyint(1) DEFAULT NULL,
  `RELE_4` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

--
-- Volcar la base de datos para la tabla `tabla01`
--

INSERT INTO `tabla01` (`RELE_1`, `RELE_2`, `RELE_3`, `RELE_4`) VALUES
(1, 1, 0, 0);

Mejor olvidar entonces el modelo:

-- Base de datos: `prueba01`
--

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

--
-- Estructura de tabla para la tabla `dispositivos`
--

CREATE TABLE IF NOT EXISTS `dispositivos` (
`ID` int(11) NOT NULL,
`nombre` varchar(30) NOT NULL,
`estado` tinyint(3) unsigned NOT NULL DEFAULT '0',
`descripcion` varchar(200) NOT NULL,
PRIMARY KEY (`ID`),
FULLTEXT KEY `nombre` (`nombre`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Volcar la base de datos para la tabla `dispositivos`
--

INSERT INTO `dispositivos` (`ID`, `nombre`, `estado`, `descripcion`) VALUES
(1, 'RELE 1', 0, 'Motor 1'),
(2, 'RELE 2', 0, 'Motor 2'),
(3, 'RELE 3', 0, 'Alarma'),
(4, 'RELE 4', 0, 'WebCam');

Mi fin es hacer auditoría si es posible. En caso de que sepas hacer mejores tablas que yo de principio a fin con triggers incluido, no te cortes en mostrarlos.