MySQL Trigger: Prevent Insert by IF statement

Question

I try to write my first trigger for a MySQL Database. It should prevent an insert (and later also updates) on "course_student" in context of the current time and the given timestamps in "capacity", but i'm still getting a syntax error.

DELIMITER $$

CREATE TRIGGER checkSubscribeTimeCourse 
BEFORE INSERT ON course_student
REFERENCING NEW AS new
FOR EACH ROW 

BEGIN

    IF (SELECT COUNT(*) FROM capacity c, course_capacity cc 
        WHERE c.cid = cc.cid 
        AND cc.cid = new.cid
        AND (c.end >= CURRENT_TIMESTAMP OR c.end IS NULL)
        AND (c.start <= CURRENT_TIMESTAMP OR c.start IS NULL)<1)
    THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error'
    END IF

END

DELIMITER ;

I translated all names; so maybe there are typing errors.

Drew · Accepted Answer · 2016-09-25 20:04:33Z

2

You have a few missing commas and some made up sql and a missing delimter at the end.

DELIMITER $$

CREATE TRIGGER checkSubscribeTimeCourse 
BEFORE INSERT ON course_student
FOR EACH ROW
BEGIN

    IF (SELECT COUNT(*) FROM capacity c, course_capacity cc 
        WHERE c.cid = cc.cid 
        AND cc.cid = new.cid
        AND (c.end >= CURRENT_TIMESTAMP OR c.end IS NULL)
        AND (c.start <= CURRENT_TIMESTAMP OR c.start IS NULL)<1)
    THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error';
    END IF;

END$$

DELIMITER ;

answered Sep 25, 2016 at 20:04

Drew

25k10 gold badges47 silver badges81 bronze badges

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

Collectives™ on Stack Overflow

MySQL Trigger: Prevent Insert by IF statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related