I doubt how to use the IF in a TRIGGER in mysql

Question

I doubt how to use the IF in a TRIGGER in mysql

Navigation

#1 by (1 votes)

0

Hello! My problem is the following I have two tables, one table called Notes and another called Students . I need to make a comparison with the child_id in the notes table and the id in the students table to only if the < in table in the students table you can enter the note value in the Notes table. I would like to know how I could do this.

mysql if trigger tabela

asked by anonymous 06.07.2016 / 00:31

1 answer

hide option with javascript Format a string that matcher found

score 1 · Answer 1

MySQL triggers do not allow an INSERT to be aborted, as in other DBMS such as PostgreSQL.

What you can do is check with an AFTER INSERT trigger (triggered after inserting the line) if the inserted student's code is valid, and if not, you can perform a DELETE (a primary key is required to perform this DELETE with security).

For example:

CREATE TRIGGER tg_insert_notas 
AFTER INSERT ON notas
FOR EACH ROW
BEGIN
    DECLARE aluno_existe BOOLEAN;

    SELECT EXISTS(SELECT 0 FROM alunos WHERE alunos.id = NEW.id_aluno) INTO aluno_existe;

    IF NOT aluno_existe THEN
        DELETE FROM notas WHERE notas.id = NEW.id;
    END IF;
END//