Cross Reference - Foreign Keys Help.....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cross Reference - Foreign Keys Help.....

Author

Topic

AJSkydiver
Starting Member

8 Posts

Posted - 2008-08-20 : 10:46:42

Hello all. I am new to SQL 2005. I hope someone can guide me to the right path. I have a user table called Lookup_UserID with the fields UserID, Name, IsActive. I have several tables that need to validate the userid for ‘CreatedOn’ constraint on these tables. An example of one of the tables is

[dbo].[Division](
[DivisionID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NOT NULL,
[Name] [varchar](100) NOT NULL,
[IsPrimary] [bit] NOT NULL,
[CreatedByUserID] [int] NOT NULL,
[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_Division_CreatedOn] DEFAULT (getdate()),
[UpdatedByUserID] [int] NULL,
[UpdatedOn] [datetime] NULL,
[IsActive] [bit] NOT NULL

My issue is that I need to create two foreign keys (CreatedByUserID, and UpdatedByUserID – with Lookup_UserID table, User ID as the primary key). I have several tables needed with this same architecture. Can someone tell me how I can accomplish this by using a trigger or view as a work around as that’s the closest I’m seeing in forums as how to do this. But, I am not sure of where to go and how to implement this…. Thank you in advance!!!! Any help, guidance or answers greatly appreciated….

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 11:46:57

i think you need a trigger for doing this. just use SUSER_SNAME() to get the user info and use this to lookup in Lookup_UserID table to get ID values. it will be something like


CREATE TRIGGER UpdateUserInfo
ON YourTable
AFTER INSERT,UPDATE
AS
BEGIN
DECLARE @User_ID int

SELECT @User_ID=User_ID
FROM Lookup_UserID
WHERE User_Name=SUSER_SNAME()

UPDATE t
SET t.CreatedByUserID=CASE WHEN d.PK IS NULL THEN @User_ID END,
t.UpdatedByUserID=@User_ID 
FROM YourTable t
INNER JOIN INSERTED i
ON i.PK=t.PK
LEFT JOIN DELETED d
ON d.PK=t.PK
END

where PK is your primary key

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-20 : 11:54:40

Based on your question I don't see that you are trying to discover the UserID...It sounds like you just need a standard Foreign Key constraint, right?


       [CreatedByUserID] [int] NOT NULL references Lookup_UserID(userid),
       [CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_Division_CreatedOn] DEFAULT (getdate()),
       [UpdatedByUserID] [int] NULL references Lookup_UserID(userid) )

If I'm probably missing something just ignore

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources