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
– Sebastian M, Commented Feb 10, 2016 at 0: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
– Darwin von Corax, Commented Feb 10, 2016 at 0:48
You shouldn't edit your question if you already have valid answers, such that your edit invalidates those answers. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Feb 11, 2016 at 15:49
This post solved it for me. stackoverflow.com/questions/18379137/… — Sebastian M
– Sebastian M, Commented Feb 11, 2016 at 21:18

jpw · Accepted Answer · 2016-02-10 01:00:09Z

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, 2016 at 1:00

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sebastian M Over a year ago

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

jpw Over a year ago

@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: stackoverflow.com/questions/8154158/…

Sebastian M Over a year ago

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.

Collectives™ on Stack Overflow

SQL Trigger Syntax

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related