插入触发器(SQL 2005)
插入触发器(SQL 2005)
提问于 2009-11-13 01:16:55
我有一个临时表(question_desc, ans1, ans2, ans3, ans4, correct_ans, marks)，其中有10个条目。
在这个表中，我必须在另外两个表中插入值：
questions (q_id(auto-generated), que_desc, marks)
answers (ans_id(auto_generated), q_id(FK), ans_desc, istrue)
对于在questions表中的每个插入，在answers表中应该有四个插入，istrue位将被设置为1以获得正确的答案。
与具有corr_ans 1的问题1类似，四个条目将是
(1,1,djhjfj,1),
(2,1,hdjfsh,0),
(3,1,hsssh,0),
(4,1,jfsh,0)在questions表中插入一行后，我需要获取q_id并在插入4条回答记录时使用它。
我尝试使用insert触发器，但它不起作用，因为它是语句级触发器，而不是行级触发器。我正在使用SQL2005。请帮帮忙。
Stack Overflow用户
发布于 2009-11-13 01:53:34
已更新。原来的答案被往下推。
既然您可以访问工作表和主题信息，那么此更改将如何处理？我在ExSheet/ExSubject的临时表中添加了一个主键。
(我知道我没有回答您的启发式问题，但我们需要首先了解问题。)
代码
SET NOCOUNT ON
USE tempdb
GO
IF OBJECT_ID('tempdb..#q1') IS NOT NULL
DROP TABLE #q1
IF OBJECT_ID('tempdb..#questions') IS NOT NULL
DROP TABLE #questions
IF OBJECT_ID('tempdb..#answers') IS NOT NULL
DROP TABLE #answers
CREATE TABLE #q1 (
ExSheet int,
ExSubject varchar(10),
question_desc varchar(70),
ans1 varchar(20),
ans2 varchar(20),
ans3 varchar(20),
ans4 varchar(20),
correct_ans varchar(20),
marks varchar(20),
CONSTRAINT PK_#q1 PRIMARY KEY (ExSheet, ExSubject)
)
CREATE TABLE #questions (
q_id int identity,
que_desc varchar(70),
marks varchar(20)
)
CREATE TABLE #answers (
ans_id int identity,
q_id int,
ans_desc varchar(20),
istrue bit
)
INSERT INTO #q1 VALUES (
1, -- ExSheet
'Subject', -- ExSubject
'Which of the following SQL statements selects the string ''Success''?',
'ans1',
'ans2',
'ans3',
'ans4',
'ans1',
'marks'
)
DECLARE @q_id int
INSERT INTO #questions (
que_desc,
marks
)
SELECT
question_desc,
marks
FROM #q1
WHERE ExSheet = 1
AND ExSubject = 'Subject'
SELECT @q_id = SCOPE_IDENTITY()
-- ans1
INSERT INTO #answers (
q_id,
ans_desc,
istrue
)
SELECT
@q_id,
ans1,
CASE WHEN ans1 = correct_ans THEN 1 ELSE 0 END
FROM #q1
WHERE ExSheet = 1
AND ExSubject = 'Subject'
-- ans2
INSERT INTO #answers (
q_id,
ans_desc,
istrue
)
SELECT
@q_id,
ans2,
CASE WHEN ans2 = correct_ans THEN 1 ELSE 0 END
FROM #q1
WHERE ExSheet = 1
AND ExSubject = 'Subject'
-- ans3
INSERT INTO #answers (
q_id,
ans_desc,
istrue
)
SELECT
@q_id,
ans3,
CASE WHEN ans3 = correct_ans THEN 1 ELSE 0 END
FROM #q1
WHERE ExSheet = 1
AND ExSubject = 'Subject'
-- ans4
INSERT INTO #answers (
q_id,
ans_desc,
istrue
)
SELECT
@q_id,
ans4,
CASE WHEN ans4 = correct_ans THEN 1 ELSE 0 END
FROM #q1
WHERE ExSheet = 1
AND ExSubject = 'Subject'
SELECT * FROM #questions
SELECT * FROM #answers结果
q_id que_desc marks
---- ------------------------------------------------------------------- -----
1 Which of the following SQL statements selects the string 'Success'? marks
ans_id q_id ans_desc istrue
------ ---- -------- ------
1 1 ans1 1
2 1 ans2 0
3 1 ans3 0
4 1 ans4 0我很确定这不是你想要的解决方案，但我们需要帮助解决这个问题。
请查看此代码，并解释需要更改哪些内容才能适用于您的解决方案。
代码
SET NOCOUNT ON
USE tempdb
GO
IF OBJECT_ID('tempdb..#q1') IS NOT NULL
DROP TABLE #q1
IF OBJECT_ID('tempdb..#questions') IS NOT NULL
DROP TABLE #questions
IF OBJECT_ID('tempdb..#answers') IS NOT NULL
DROP TABLE #answers
CREATE TABLE #q1 (
question_desc varchar(20),
ans1 varchar(20),
ans2 varchar(20),
ans3 varchar(20),
ans4 varchar(20),
correct_ans varchar(20),
marks varchar(20)
)
CREATE TABLE #questions (
q_id int identity,
que_desc varchar(20),
marks varchar(20)
)
CREATE TABLE #answers (
ans_id int identity,
q_id int,
ans_desc varchar(20),
istrue bit
)
INSERT INTO #q1 VALUES ('Question 01', 'ans1', 'ans2', 'ans3', 'ans4', 'ans1', 'marks')
DECLARE @q_id int
INSERT INTO #questions (
que_desc,
marks
)
SELECT
question_desc,
marks
FROM #q1
WHERE question_desc = 'Question 01'
SELECT @q_id = SCOPE_IDENTITY()
-- ans1
INSERT INTO #answers (
q_id,
ans_desc,
istrue
)
SELECT
@q_id,
ans1,
CASE WHEN ans1 = correct_ans THEN 1 ELSE 0 END
FROM #q1
WHERE question_desc = 'Question 01'
-- ans2
INSERT INTO #answers (
q_id,
ans_desc,
istrue
)
SELECT
@q_id,
ans2,
CASE WHEN ans2 = correct_ans THEN 1 ELSE 0 END
FROM #q1
WHERE question_desc = 'Question 01'
-- ans3
INSERT INTO #answers (
q_id,
ans_desc,
istrue
)
SELECT
@q_id,
ans3,
CASE WHEN ans3 = correct_ans THEN 1 ELSE 0 END
FROM #q1
WHERE question_desc = 'Question 01'
-- ans4
INSERT INTO #answers (
q_id,
ans_desc,
istrue
)
SELECT
@q_id,
ans4,
CASE WHEN ans4 = correct_ans THEN 1 ELSE 0 END
FROM #q1
WHERE question_desc = 'Question 01'
SELECT * FROM #questions
SELECT * FROM #answers结果
q_id que_desc marks
---- ----------- -----
1 Question 01 marks
ans_id q_id ans_desc istrue
------ ---- -------- ------
1 1 ans1 1
2 1 ans2 0
3 1 ans3 0
4 1 ans4 0页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1723877
复制相关文章
相似问题
腾讯云开发者
