有朋友跟我发过这个,但是有错误。代码如下:--触发器 CREATE TRIGGER tr_Question_ins ON Question INSTEAD OF INSERT AS BEGIN INSERT INTO Question(QNo,Qclass,Qdifficulty,Qabout,Qdegree,Qdetail,QoptionsA,QoptionsB,QoptionsC,QoptionsD,Qanswer) SELECT (CASE WHEN Qclass=N'选择' THEN 'XZ' WHEN Qclass=N'判断' THEN 'PD' WHEN Qclass=N'简答' THEN 'JD' WHEN Qclass=N'计算' THEN 'JS' END)+ RIGHT('000'+convert(varchar,ROW_NUMBER() OVER(PARTITION BY a.Qclass ORDER BY a.Qno)+b.Qno),4),a.Qclass,a.Qdifficulty,a.Qabout,a.Qdegree,a.Qdetail,a.QoptionsA,a.QoptionsB,a.QoptionsC,a.QoptionsD,a.Qanswer FROM INSERTED a JOIN ( SELECT Qclass,CONVERT(INT,RIGHT(MAX(QNo),4))QNo FROM Question GROUP BY Qclass )b ON a.Qclass=b.Qclass end CHECK(Qclass=N'选择' AND QoptionsA IS NOT NULL AND QoptionsB IS NOT NULL AND QoptionsC IS NOT NULL AND QoptionsD IS NOT NULL) OR (Qclass<>N'选择' AND QoptionsA IS NULL AND QoptionsB IS NULL AND QoptionsC IS NULL AND QoptionsD IS NULL)也不知道怎么改
CREATE TRIGGER tr_Question_ins ON Question INSTEAD OF INSERT AS BEGIN INSERT INTO Question(QNo,Qclass,Qdifficulty,Qabout,Qdegree,Qdetail,QoptionsA,QoptionsB,QoptionsC,QoptionsD,Qanswer) SELECT (CASE WHEN a.Qclass=N'选择' THEN 'XZ' WHEN a.Qclass=N'判断' THEN 'PD' WHEN a.Qclass=N'简答' THEN 'JD' WHEN a.Qclass=N'计算' THEN 'JS' END)+ RIGHT('000'+convert(varchar,ROW_NUMBER() OVER(PARTITION BY a.Qclass ORDER BY a.Qno)+b.Qno),4),a.Qclass,a.Qdifficulty,a.Qabout,a.Qdegree,a.Qdetail,a.QoptionsA,a.QoptionsB,a.QoptionsC,a.QoptionsD,a.Qanswer FROM INSERTED a JOIN ( SELECT Qclass,CONVERT(INT,RIGHT(MAX(QNo),4))QNo FROM Question GROUP BY Qclass )b ON a.Qclass=b.Qclass end呃,试试这个
CHECK(Qclass=N'选择' AND QoptionsA IS NOT NULL AND QoptionsB IS NOT NULL AND QoptionsC IS NOT NULL AND QoptionsD IS NOT NULL) OR (Qclass<>N'选择' AND QoptionsA IS NULL AND QoptionsB IS NULL AND QoptionsC IS NULL AND QoptionsD IS NULL)这一段有错误是怎么回事 啊?错误如下: 消息 156,级别 15,状态 1,第 1 行 关键字 'CHECK' 附近有语法错误。
TO:#8 这个要放到ALTER TABLE Question 里ALTER TABLE Question ADD CONSTRAINT [CK_Question_QclassAndABCD] CHECK(Qclass=N'选择' AND QoptionsA IS NOT NULL AND QoptionsB IS NOT NULL AND QoptionsC IS NOT NULL AND QoptionsD IS NOT NULL) OR (Qclass<>N'选择' AND QoptionsA IS NULL AND QoptionsB IS NULL AND QoptionsC IS NULL AND QoptionsD IS NULL)
不好意思,结贴时突然看到的,我执行了下怎么出错了 消息 156,级别 15,状态 1,第 3 行 关键字 'OR' 附近有语法错误。ALTER TABLE Question ADD CONSTRAINT [CK_Question_QclassAndABCD] CHECK((Qclass=N'选择' AND QoptionsA IS NOT NULL AND QoptionsB IS NOT NULL AND QoptionsC IS NOT NULL AND QoptionsD IS NOT NULL) OR (Qclass<>N'选择' AND QoptionsA IS NULL AND QoptionsB IS NULL AND QoptionsC IS NULL AND QoptionsD IS NULL))没注意,那就在外面加个括号
CREATE TRIGGER tr_Question_ins
ON Question
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Question(QNo,Qclass,Qdifficulty,Qabout,Qdegree,Qdetail,QoptionsA,QoptionsB,QoptionsC,QoptionsD,Qanswer)
SELECT (CASE WHEN Qclass=N'选择' THEN 'XZ'
WHEN Qclass=N'判断' THEN 'PD'
WHEN Qclass=N'简答' THEN 'JD'
WHEN Qclass=N'计算' THEN 'JS'
END)+
RIGHT('000'+convert(varchar,ROW_NUMBER() OVER(PARTITION BY a.Qclass ORDER BY a.Qno)+b.Qno),4),a.Qclass,a.Qdifficulty,a.Qabout,a.Qdegree,a.Qdetail,a.QoptionsA,a.QoptionsB,a.QoptionsC,a.QoptionsD,a.Qanswer
FROM INSERTED a
JOIN (
SELECT Qclass,CONVERT(INT,RIGHT(MAX(QNo),4))QNo FROM Question GROUP BY Qclass
)b ON a.Qclass=b.Qclass
end
CHECK(Qclass=N'选择' AND QoptionsA IS NOT NULL AND QoptionsB IS NOT NULL AND QoptionsC IS NOT NULL AND QoptionsD IS NOT NULL)
OR (Qclass<>N'选择' AND QoptionsA IS NULL AND QoptionsB IS NULL AND QoptionsC IS NULL AND QoptionsD IS NULL)也不知道怎么改
列名 'Qclass' 不明确。
消息 209,级别 16,状态 1,过程 tr_Question_ins,第 9 行
列名 'Qclass' 不明确。
消息 209,级别 16,状态 1,过程 tr_Question_ins,第 10 行
列名 'Qclass' 不明确。
消息 209,级别 16,状态 1,过程 tr_Question_ins,第 11 行
列名 'Qclass' 不明确。
---怎么办
ON Question
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Question(QNo,Qclass,Qdifficulty,Qabout,Qdegree,Qdetail,QoptionsA,QoptionsB,QoptionsC,QoptionsD,Qanswer)
SELECT (CASE WHEN a.Qclass=N'选择' THEN 'XZ'
WHEN a.Qclass=N'判断' THEN 'PD'
WHEN a.Qclass=N'简答' THEN 'JD'
WHEN a.Qclass=N'计算' THEN 'JS'
END)+
RIGHT('000'+convert(varchar,ROW_NUMBER() OVER(PARTITION BY a.Qclass ORDER BY a.Qno)+b.Qno),4),a.Qclass,a.Qdifficulty,a.Qabout,a.Qdegree,a.Qdetail,a.QoptionsA,a.QoptionsB,a.QoptionsC,a.QoptionsD,a.Qanswer
FROM INSERTED a
JOIN (
SELECT Qclass,CONVERT(INT,RIGHT(MAX(QNo),4))QNo FROM Question GROUP BY Qclass
)b ON a.Qclass=b.Qclass
end呃,试试这个
OR (Qclass<>N'选择' AND QoptionsA IS NULL AND QoptionsB IS NULL AND QoptionsC IS NULL AND QoptionsD IS NULL)这一段有错误是怎么回事 啊?错误如下:
消息 156,级别 15,状态 1,第 1 行
关键字 'CHECK' 附近有语法错误。
CHECK(Qclass=N'选择' AND QoptionsA IS NOT NULL AND QoptionsB IS NOT NULL AND QoptionsC IS NOT NULL AND QoptionsD IS NOT NULL)
OR (Qclass<>N'选择' AND QoptionsA IS NULL AND QoptionsB IS NULL AND QoptionsC IS NULL AND QoptionsD IS NULL)
消息 156,级别 15,状态 1,第 3 行
关键字 'OR' 附近有语法错误。ALTER TABLE Question ADD CONSTRAINT [CK_Question_QclassAndABCD]
CHECK((Qclass=N'选择' AND QoptionsA IS NOT NULL AND QoptionsB IS NOT NULL AND QoptionsC IS NOT NULL AND QoptionsD IS NOT NULL)
OR (Qclass<>N'选择' AND QoptionsA IS NULL AND QoptionsB IS NULL AND QoptionsC IS NULL AND QoptionsD IS NULL))没注意,那就在外面加个括号