Showing posts with label differentissues. Show all posts
Showing posts with label differentissues. Show all posts

Saturday, February 25, 2012

Conditional For Insert Trigger

Sort of new to this, hoping for some help here.
I've got a table which you insert Scores and comments for Different
Issues. Each User may only input ONE score, but many comments per
issue.
I'm attempting to run a trigger which looks at the distinct users (in
the table) and then will either allow the comment AND score to be
Inserted if the User hasn't put in a score for that issue yet. OR if
the User already HAS input a Score, set the Score to NULL and allow the
rest of the record to be inserted.
My trigger works, but instead of letting the *New* User to put in the
score, it just nulls it.
Sorry if this is kind of long, but I hope I've explained it
sufficiently. I hope someone can help me out. Thanks in advance
This is my trigger :
CREATE TRIGGER trCheckUserResponses
ON dbo.tblIssues_Responses
FOR INSERT
AS
IF (SELECT UserID from Inserted) not in (select distinct UserID from
tblIssues_responses)
BEGIN
Update tblIssues_Responses
Set tblIssues_Responses.Score= Inserted.Score
From tblIssues_Responses AS A, Inserted AS B
Where A.Response_ID = B.Response_ID
END
ELSE
BEGIN
Update tblIssues_Responses
Set tblIssues_Responses.Score = NULL
From tblIssues_Responses AS A, Inserted AS B
Where A.Response_ID = B.Response_ID
ENDPlease post DDL and sample data.
ML
http://milambda.blogspot.com/