MariaDB Trigger joining two tables?

Question

If you have two tables (course and teacher)

table course contains: C#, course_name, teacher, supervisor
table teacher contains:T#, teacher_name, department

Teacher and supervisor are foreign keys meaning they contain T#.

Now i want to make a trigger that checks if teacher and supervisor are both from the same department (mathematics, programming etc.), if not then an error should show. It should be an "insert" of a new course trigger.

What do you have so far? Something like `(SELECT COUNT(DISTINCT department) FROM teacher WHERE T# IN (NEW.teacher, NEW.supervisor)) <> 1)` should work as a check within the trigger. — Uueerdo, Feb 05 '20 at 23:48
This is your phpmyadmin version. To see your MySQL version, you can run `select version()` — GMB, Feb 05 '20 at 23:49
That's a MariaDB version, not a MySQL version. I've updated the question's tags. — Bill Karwin, Feb 05 '20 at 23:57
You are reaching into "that does not belong in the db" areas. Business logic should be in the application layer. The database is a "repository" and the "source of truth". — Rick James, Feb 12 '20 at 04:29

GMB · Accepted Answer · 2020-02-06T00:01:31.070

0

You could use the following trigger:

delimiter //
create trigger trg_ins_course_teacher_supervisor 
before insert on course
for each row
begin
    if 
        (select t.department from teacher t where t.t# = new.teacher)
        != (select t.department from teacher t where t.t# = new.supervisor)
    then
        signal 
            sqlstate '45000' 
            set message_text = 'teacher and supervisor must belong to the same department';
    end if;
end
//
delimiter ;

edited Feb 06 '20 at 00:01

answered Feb 05 '20 at 23:48

GMB

216,147
25
84
135

OP is looking to determine if the departments of the teacher and supervisor are the same, not whether the teacher and supervisor are. – Uueerdo Feb 05 '20 at 23:53
@Uueerdo: ah, my bad... Thanks. – GMB Feb 05 '20 at 23:55
@Uueerdo: I (think I) fixed this. – GMB Feb 05 '20 at 23:58
yeah Uueerdo is right, im looking to make sure the department of both teacher and supervisor is the same – Yulaw16 Feb 06 '20 at 00:01
@Yulaw16: yes. That's what the trigger does now. – GMB Feb 06 '20 at 00:02
Yes this worked!! thank you so much, I was stuck for 2 days before signing up here :D – Yulaw16 Feb 06 '20 at 00:11
1

@GMB yep, that would have been my other suggestion for the condition to check. – Uueerdo Feb 06 '20 at 00:22

MariaDB Trigger joining two tables?

1 Answers1