December 23, 2002 at 12:32 pm
Hello, I have a simple table (3 columns)
m_Name
m_Group
m_Date
I am inserting rows when a user logs into a specific website. I get the name from the login and I get the current date using a Default of GetDate().
What I don't know how to do is get the m_Group column to compute based on a simple select statement from another table.
select Item1 from TABLE1, TABLE2 where m_Name = Item2
If anyone knows of a simple way for me to do this, I would really appreciate your help. I thought I would have to create a UserDefinedFunction and then create a Default with that function, but I am not sure how to do that.
Thanks.
Jimmy
Edited by - dcjimmy on 12/23/2002 12:34:28 PM
December 23, 2002 at 4:43 pm
This trigger should work. xblt1 and xtbl2 are you TABLE1 and TABLE2
CREATE TRIGGER DetermineGroup
ON dbo.xtbl1
FOR INSERT
AS
Declare @sName char(10), @Group char(10)
Select @sName=I.m_Name
From xtbl1 S, INSERTED I
Where S.m_Name=I.m_Name
Select @Group = Item2
from xtbl2
where Item1 = @sName
Update xtbl1
Set m_Group = @Group
where m_Name=@sName
Everett Wilson
ewilson10@yahoo.com
December 23, 2002 at 6:42 pm
Or better to prevent the extra updates that will occurr do to the trigger wrap the logic in a Stored Procedure instead of writing to the table and get the data then insert all at once.
Ex.
CREATE PROCEDURE usp_UserLogin
@sName char(10)
AS
SET NOCOUNT ON
DECLARE @Group char(10
Select @Group = Item2
from xtbl2
where Item1 = @sName
INSERT xtbl1 (m_Name, M_Group)
VALUES (@sName, @Group)
Will take better advantage of stored execution plans, table is better protected as you give execute rigths to the SP and no rights to the table, and on the web side you can use command objects with no recordset returns to cut down so overhead.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply