Doubt how to use IF in a TRIGGER in mysql

Asked

Viewed 4,352 times

0

Hello! My problem is the following I have two tables, a table called notes and another call students. I need to make a comparison with the id_student on the table notes and the id present in the table students only if the id exist in the table students can enter the value note on the table notes. I wonder how I could do that.

mysql if table trigger

  • Bruno, welcome to Soft, go to Help and take the Tour, then edit your question showing part of your code,

1 answer

1

Mysql triggers do not allow an INSERT to be aborted, as in other DBMS like Postgresql.

What you can do is check with a Trigger AFTER INSERT (fired after entering the line) if the student’s code entered is valid, and if it is not you can perform a DELETE (a primary key is required to perform this DELETE safely).

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

Browser other questions tagged mysql if table trigger

You are not signed in. Login or sign up in order to post.