MySQL Trigger：阻止IF语句插入 [英] MySQL Trigger: Prevent Insert by IF statement

问题描述

我尝试编写MySQL数据库的第一个触发器。在当前时间的上下文中阻止插入（以及稍后还会更新） course_student 以及容量中的给定时间戳，但我仍然会收到语法错误。

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 ;

我翻译了所有名字;所以也许有打字错误。

推荐答案

你有一些缺少逗号，有些编造了sql而且丢失了delimter末。

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 ;

这篇关于MySQL Trigger：阻止IF语句插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！