mysql - SQL 引用完整性触发器

Question

创建触发器 FK_INTEGRITY_CHECK
在插入 sch_book_list 之前
对于每一行开始
选择案例
WHEN ((从sch_resource中选择resource_cd，其中resource_cd = NEW.resource_cd) IS NULL)
THEN RAISE(ABORT, '在表“sch_book_list”上插入违反了外键'||'约束“FK9qti8knho9i047jd1lrwqwy2x”')
结尾
结束；

This is my trigger code but there is some syntax error. I don't know what the error is?

Answer 1

您可以将计数存储在变量中并检查值，而不是使用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;