SQL Trigger Syntax

Question

Each course on my DB has a correspondent faculty_id.

Each faculty has affinities with other faculties (and as a consequense so do the courses with those faculty_ids). Faculty affinities and courses affinities are sotred y sepparated tables.

I'm trying to build a trigger which deletes records from the course_affinity table when a record on faculty_affinity is deleted.

This is what I have. It does not work.

CREATE TRIGGER deleteCourses
AFTER DELETE
ON faculty_affinities
AS
BEGIN
    DELETE FROM course_affinities
    JOIN courses AS course1
    JOIN courses AS course2
    ON course_affinities.course1_id = course1.id
    AND course_affinities.course2_id = course2.id
    WHERE (course1.faculty_id = DELETED.faculty1_id OR course1.faculty_id = DELETED.faculty2_id)
    AND (course2.faculty_id = DELETED.faculty1_id OR course2.faculty_id = DELETED.faculty2_id);
END;

Help someone? What am I doing wrong?

Edit: I get error

SQLSTATE[4200]: Syntax error... neas 'AS at line 4

Edit2: my migrations

Schema::create('courses', function (Blueprint $table) {
                $table->increments('id');
                $table->string('name', 150);
                $table->integer('faculty_id')->unsigned();
                $table->foreign('faculty_id')->references('id')->on('faculties');
                $table->boolean('active')->default(1);
                //$table->softDeletes();
            });

    Schema::create('faculties', function (Blueprint $table) {
                    $table->increments('id');
                    $table->string('name', 150)->unique();
                    $table->boolean('active')->default(1);
                    //$table->softDeletes();
            });


Schema::create('course_affinities', function (Blueprint $table) {
                $table->increments('id');
                $table->integer('course1_id')->unsigned();
                    $table->foreign('course1_id')->references('id')->on('courses');
                $table->integer('course2_id')->unsigned();
                    $table->foreign('course2_id')->references('id')->on('courses');
                $table->boolean('active')->default(1);
                //$table->softDeletes();
            });

Schema::create('faculty_affinities', function (Blueprint $table) {
                $table->increments('id');
                $table->integer('faculty1_id')->unsigned();
                    $table->foreign('faculty1_id')->references('id')->on('faculties');
                $table->integer('faculty2_id')->unsigned();
                    $table->foreign('faculty2_id')->references('id')->on('faculties');
                $table->boolean('active')->default(1);
                //$table->softDeletes();
            });

Syntax error. I edited the original post with the error message. — Sebastian M, Feb 10 '16 at 00:46
I don't think you can delete from a `JOIN`, and I know `DELETED` is not a valid keyword in MySQL. If you post the table definitions maybe we can guide you in the right direction. — Darwin von Corax, Feb 10 '16 at 00:48
You shouldn't *edit* your question if you already have valid answers, such that your edit *invalidates* those answers. — Damien_The_Unbeliever, Feb 11 '16 at 15:49
This post solved it for me. http://stackoverflow.com/questions/18379137/creating-mysql-procedure-in-laravel-4-migrations — Sebastian M, Feb 11 '16 at 21:18

score 1 · Answer 1 · answered Feb 10 '16 at 01:00

1

Your code looks like it might have been written for MS SQL Server and not MySQL (the use of the deleted virtual table hints at that); the corresponding MySQL syntax should be as follows:

CREATE TRIGGER deleteCourses
AFTER DELETE ON faculty_affinities
FOR EACH ROW
BEGIN
    DELETE ca
    FROM course_affinities ca
    JOIN courses AS course1 ON ca.course1_id = course1.id 
    JOIN courses AS course2 ON ca.course2_id = course2.id    
    WHERE (course1.faculty_id = OLD.faculty1_id OR course1.faculty_id = OLD.faculty2_id)
      AND (course2.faculty_id = OLD.faculty1_id OR course2.faculty_id = OLD.faculty2_id);
END;

I haven't tested it though so proceed with some care...

answered Feb 10 '16 at 01:00

jpw

44,361
6
66
86

Thank! That looks good. I'm still getting an error though: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 10 – Sebastian M Feb 10 '16 at 11:35
@JulianM. You might need to change the statement delimiter to something other than semi-colon or it should work if you remove the semi-colon at the end of line 10 (after the where clauses). See this question for examples: http://stackoverflow.com/questions/8154158/mysql-how-do-i-use-delimiters-in-triggers – jpw Feb 10 '16 at 13:19
The weird thing is that if I run this query directly in phpmyadmin or in the MySQL console, I get no errors. I only get the error when running migration on artisan. However, I edited my post with my current trigger. – Sebastian M Feb 11 '16 at 15:42

SQL Trigger Syntax

1 Answers1