Trigger Question!

Greetings StackOverflow community!

For this new project I'm required to write a trigger which fires when a row is inserted into one of the tables.

I have a table called Questions which contains the following fields:

• ID - int
• Datecreated - smalldatetime
• Category_ID - int
• Value - ntext
• Timelimit - smalldatetime
• helper - ntext (nullable)

and another table User_Questions which contains the following fields:

• ID - int
• Question_ID - int
• User_ID - int
• Datecreated - smalldatetime
• helper - ntext (nullable).

Now I think i can write a trigger which extracts the Datecreated and ID fields from Questions table and adds them to a new row in the Users_Questions table. Could you please advice me o开发者_如何学JAVAn how to get the value for the User_ID field?

That would be greatly appreciated.

Thank you very much in advance!

---

One option that I would prefer in this case would be to create a stored procedure that takes the necessary input parameters, and then does your two inputs into the two tables in a transaction. That way you control what's going on:

(this assumes SQL Server 2005, and the ID fields are INT IDENTITY - correct??)

 CREATE PROCEDURE dbo.InsertQuestion
    @Datecreated SMALLDATETIME, @Category_ID INT,
    @Question NVARCHAR(MAX), @Timelimit SMALLDATETIME,
    @helper NVARCHAR(MAX)
 AS BEGIN
    -- start transaction and a TRY..CATCH block
    BEGIN TRANSACTION
    BEGIN TRY
        -- insert values into "Questions" table
        INSERT INTO 
           dbo.Questions(DateCreated, Category_ID, Question, TimeLimit, Helper)
        VALUES
            (@DateCreated, @Category_ID, @Question, @TimeLimit, @Helper)

        -- retrieve the ID of the newly inserted row
        DECLARE @QuestionID INT
        SET @QuestionID = SCOPE_IDENTITY()

        -- determine the user ID from SQL Server
        DECLARE @UserID INT
        SET @UserID = SUSER_ID() 

        -- insert values into "User_Questions" table
        INSERT INTO 
           dbo.UserQuestions(QuestionID, UserID, DateCreated, Helper)
        VALUES
            (@QuestionID, @UserID, @DateCreated, @Helper)

        -- commit transaction, if everything went well
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
       -- handle your error, e.g. by logging to a table or something.....
       ROLLBACK TRANSACTION
    END CATCH
 END

Triggers are notoriously hard to get right, they don't scale very well - I would try to avoid triggers if ever possible (not always possible, but often, it is) –