SQL Server - Computed Columns - Experienced user please

[Arch4ngel]

Hi ! I've got a good question that have no anwser... (question feel lonesome).

 

I want to have a calculated column with a formula...

 

Here is the problem (more detail ? tell me)...

 

I'm making a Ask-Anwser(work like a forum with less functionnality) in ASP.NET... and I have 2 tables "tblQuestions" and "tblPosts" (well anwsers).

 

In tblQuestions... I have a field name Anwsered that will be 1 (true) if an admin anwsered the question and 0 (false) if he didn't anwser...

 

What I would like to do... is to verify tblPosts if fields UserType contain at least 1 "Admin".

 

Help would be really appreciated.

Alternative solution is also welcome as I want it to work... ABSOLUTLY :p

(short delay you know....)

 

Thank you very much for your help buddy.

[mocella]

You could create a trigger that updates your Questions table when the Posts table gets a record added.

 

You could also just have a stored proc that first inserts your Post, then proceeds to update your Question record related to the Post.

 

There's a few ways you can approach this and all will work just fine. Depends on how much code you want to write and how much authority you have on the SQL Server.

[Arch4ngel]

Found an anwser but still wanna know

 

Well... I coded the trigger one... that was the one many friend suggested me.

 

Authority ? Full.

Code ? Kiss (Keep it simple stupid)

 

But if someone still want to teach me how to use Formula... :p lol

 

Here is my SQL code for those who still want to know:

CREATE TRIGGER trig_ReponseAdmin ON [dbo].[tbPosts] 
FOR INSERT
AS
BEGIN
UPDATE tbQuestions
SET tbQuestions.Anwsered = (1)
WHERE tbQuestions.ID in 
(SELECT ID_Q FROM Inserted) And 'Admin' in 
(SELECT Sender FROM tbPosts WHERE tbPosts.ID in 
(SELECT ID FROM Inserted))
END

[Nerseus]

If you wanted to update all of tblQuestions at one time, then the "formula" approach would work well. Two possibilities come to mind: set the bit flag (you called it Answered) to the SUM of the answers that are from "Admin" or use a CASE... END to check the count(*).

 

As it is, I think the best approach would be to use triggers. Or, if you frown on triggers, then in the "insert an answer" proc. If you wanted to keep it simple, you would do:

 

* insert into tblQuestions - always default "Answered" to false.

* insert into tblPosts - in the insert trigger, if the sender was "Admin" then update the tblQuestions row to true. At worse case, a second post by an admin might update the column to true when it already was true - no big deal in my book. Certainly better than having to do the subselects each time.

 

A couple of unsolicitied pieces of advise (ignore if you want): you might want to do some reading up on naming conventions if you're going to be designing tables and columns. For example, tables are generally named singular - so tblQuestion instead of tblQuestions. Also, "Answered" to me means that there's at least one tblPosts row. Maybe the column should be something like AnsweredByAdmin or ReplyFromAdmin.

 

-Nerseus

[Arch4ngel]

Ya thanks Nerseus ! I maked it worked with triggers. I had a short schedule so I didn't have time to really look at Formula (might have been nice).

 

For the naming convention I completly agree with you... but I have big difficulty to stick to it myself ! lol :p

 

tbl instead of tb..... and stick to singular !.... I'll try to remember !

 

For the field Answered... yeah I could have been more explicit... maybe I'll rename it, regenerate my 3 DataSet... 3 DataAdapters... my XSD... etc... :p

 

Might be a good thing to do ! Thanks again Nerseus!

Professional answer as always ! (good to hear that some of us are "professional" no ?)