Inserting trigger (SQL 2005)

I have a temp table (question_desc, ans1, ans2, ans3, ans4, correct_ans, marks) with say 10 entries.

From this table I have to insert values in two other tables:

questions (q_id(auto-generated), que_desc, marks)

answers (ans_id(auto_generated), 开发者_运维知识库q_id(FK), ans_desc, istrue)

For each insert in questions table there should be four inserts in answers table and istrue bit will be set 1 for correct answer.

Like for question 1 with corr_ans 1, four entries will be

(1,1,djhjfj,1),
(2,1,hdjfsh,0),
(3,1,hsssh,0),
(4,1,jfsh,0)

After one row is inserted in questions table I need to fetch the q_id and use it while inserting 4 answer records.

I tried using insert trigger but it doesn't work as it is statement level trigger, not row level. I am using SQL 2005. Please help.

Here is the basic idea, try to incorporate this into the rest of your app.

CREATE PROCEDURE dbo.funnyInsert(
@question_desc varchar(100)
,@ans1 varchar(100)
,@ans2 varchar(100)
,@ans3 varchar(100)
,@ans4 varchar(100)
,@corect_ans varchar(100)
,@mark numeric(4,1)
)
AS
BEGIN
    DECLARE @lastID int

    INSERT INTO dbo.questions(que_desc, mark)
        VALUES(@question_desc, @mark);

    SET @lastID = IDENT_CURRENT('dbo.questions')

    INSERT INTO dbo.answers(q_id,ans_desc, isTrue)
        VALUES(@lastID, @ans1,
        CASE WHEN @ans1 = @corect_ans THEN 1 ELSE 0 END);

    INSERT INTO dbo.answers(q_id,ans_desc, isTrue)
        VALUES(@lastID, @ans2,
        CASE WHEN @ans2 = @corect_ans THEN 1 ELSE 0 END);

    INSERT INTO dbo.answers(q_id,ans_desc, isTrue)
        VALUES(@lastID, @ans3,
        CASE WHEN @ans3 = @corect_ans THEN 1 ELSE 0 END);

    INSERT INTO dbo.answers(q_id,ans_desc, isTrue)
        VALUES(@lastID, @ans4,
        CASE WHEN @ans4 = @corect_ans THEN 1 ELSE 0 END);

END

[Updated. Original answer pushed down.]

Since you have access to the sheet and subject information, what about this change? I added a PK in the temp table of ExSheet/ExSubject.

(I understand I'm not answering your trigger question, but we need to understand the problem first.)

Code

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

Result

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

I'm quite sure this is not the solution you're looking for, but we'll need help getting this figured out.

Please look over this code and explain what needs to change to work for your solution.

Code

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

Result

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

I'm not sure I would use a trigger in this case, since there don't appear to be any conditionals determining whether or not to add records to the questions and answers tables.

In the place of code that adds the one record into the temp table, I'd instead call a stored procedure, passing as parameters the 7 fields.

The stored procedure would do raw inserts into the questions and answers table (and update the temp table as well, if that is needed elsewhere).

If the problem is tracking the ID of the last inserted record in questions for use in answers, check out this article on scoping auto-generated ids: http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

EDIT: If you decide a trigger is the way to go, you can have the trigger call the stored procedure described above from inside the trigger.

Hope this is helpful.

are you just creating relatively empty answer records so that you can go back later and edit them? if so, i would just modify your insert sp to do something like this

declare @questions table (qid int identity(1,1), question nvarchar(max))
declare @answers table (aid int identity(1,1), qid int, answer nvarchar(max))


insert into @questions values ('Who?')
insert into @questions values ('What')
insert into @questions values ('When?')
insert into @questions values ('Where?')


insert into @answers (qid)
select qid
from @questions
union all
select qid
from @questions
union all
select qid
from @questions
union all
select qid
from @questions

select *
from @answers

you can also modify things so that you're only inserting questions that don't have any answers yet.