MySQL trigger before validating data

Question

I want to create a trigger that validates some data before inserting rows into the table. I have three tables:

Table staff:

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

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 only users belonging to people can insert comments, 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 user table and if it is empty then don't insert anything into the table but I can't make it work

Answer 1

You are already in a trigger inserted on Notas, so if there is no reason to cancel it, just let it pass.

You can also use IS NULL or IS NOT NULL when comparing something to NULL. Do not use = or != because NULL is not a value that is equal to or not equal to anything. ie. NULL = NULL is incorrect.

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