CREATE TRIGGER FK_INTEGRITY_CHECK
BEFORE INSERT ON sch_book_list
FOR EACH ROW BEGIN
SELECT CASE
WHEN ((SELECT resource_cd FROM sch_resource WHERE resource_cd = NEW.resource_cd) IS NULL)
THEN RAISE(ABORT, 'insert on table "sch_book_list" violates foreign key '|| 'constraint "FK9qti8knho9i047jd1lrwqwy2x"')
END
END;
这是我的触发代码,但是存在一些语法错误。我不知道错误是什么?
而不是使用CASE
,您可以将计数存储在变量中并检查值,例如:
CREATE TRIGGER FK_INTEGRITY_CHECK
BEFORE INSERT ON sch_book_list
FOR EACH ROW BEGIN
DECLARE row_count INT;
SET row_count = (SELECT COUNT(*) FROM sch_resource WHERE resource_cd = NEW.resource_cd)
IF (row_count = 0) THEN
RAISE(ABORT, 'insert on table "sch_book_list" violates foreign key '|| 'constraint "FK9qti8knho9i047jd1lrwqwy2x"');
END IF;
END;