MySQL Trigger to verify data before INSERT-CodePudding

I want to create a trigger that verifies some data before inserting a row into a table. I have three tables:

Table Personnel:

CREATE TABLE IF NOT EXISTS `mydb`.`Personal` (
  `IdPersonal` INT NOT NULL,
  `ApePaterno` VARCHAR(60) NOT NULL,
  `ApeMaterno` VARCHAR(60) NULL,
  `Nombre` VARCHAR(60) NOT NULL,
  `Direccion` VARCHAR(100) NOT NULL,
  `FechaDeIngreso` DATE NOT NULL,
  PRIMARY KEY (`IdPersonal`))

Table User:

CREATE TABLE IF NOT EXISTS `mydb`.`Usuarios` (
  `idUsuario` INT NOT NULL,
  `Nombre` VARCHAR(45) NOT NULL,
  `Contrasenia` VARCHAR(45) NOT NULL,
  `IdPersonal` INT NULL,
  PRIMARY KEY (`idUsuario`),
  INDEX `fk_Usuario_Personal_idx` (`IdPersonal` ASC) VISIBLE,
  CONSTRAINT `fk_Usuario_Personal`
    FOREIGN KEY (`IdPersonal`)
    REFERENCES `mydb`.`Personal` (`IdPersonal`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

And table Notes:

CREATE TABLE IF NOT EXISTS `mydb`.`Notas` (
  `idNota` INT NOT NULL,
  `Nota` VARCHAR(256) NOT NULL,
  `IdUsuario` INT NOT NULL,
  PRIMARY KEY (`idNota`),
  INDEX `fk_IdUsuario_idx` (`IdUsuario` ASC) VISIBLE,
  CONSTRAINT `fk_IdUsuario`
    FOREIGN KEY (`IdUsuario`)
    REFERENCES `mydb`.`Usuarios` (`idUsuario`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

So, an user can insert a note only if it is part of the personnel, so I tried to implement the next trigger:

CREATE DEFINER=`root`@`localhost` TRIGGER `Notas_BEFORE_INSERT` BEFORE INSERT ON `Notas` FOR EACH ROW BEGIN
    DECLARE IdInterno INT;
    SELECT IdPersonal INTO IdInterno
    FROM Usuarios WHERE idUsuario = new.IdUsuario;
    IF (IdInterno != null) THEN
        INSERT INTO Notas (Nota, IdUsuario)
        VALUES (new.Nota, new.IdUsuario);
    ELSE
        SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Las notas sólo pueden ser registradas por usuarios internos';
    END IF;
END

The idea is to get the Peronnel id from the users table and if it is null, don't insert anything into the table, but I can't make it work

CodePudding user response：

You're already in a trigger for insert on Notas, so if there's no reason to cancel it, just let it go through.

Also use IS NULL or IS NOT NULL when comparing something to NULL. Don't use = or !=, because NULL is not a value that is equal to or unequal to anything. I.e. NULL = NULL is not true.

CREATE DEFINER=`root`@`localhost` TRIGGER `Notas_BEFORE_INSERT` 
  BEFORE INSERT ON `Notas` FOR EACH ROW 
BEGIN
    DECLARE IdInterno INT;
    SELECT IdPersonal INTO IdInterno
    FROM Usuarios WHERE idUsuario = new.IdUsuario;
    IF (IdInterno IS NULL) THEN
        SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Las notas sólo pueden ser registradas por usuarios internos';
    END IF;
END