April 10, 2009 at 3:29 pm
What is the best way to go about getting the User ID when they create a new record to a specific table? Trigger?
April 10, 2009 at 9:29 pm
Hi,
Use the DEFAULT options, while the creating of the tables,
CREATE TABLE #ABC
(
COL1 smallint,
COL2 smallint DEFAULT USER_ID(), --Preferred default definition
COL3 char(3) DEFAULT 'xyz'--Preferred default definition
)
ARUN SAS
April 11, 2009 at 10:15 am
User ID of SQL Server account or Windows account?
April 13, 2009 at 7:00 am
SQL Server
April 13, 2009 at 7:08 am
I agree with arun.sas's approach. Maybe just consider to use an own user id instead of the system user_id to avoid problems when you move your remove/add users. You can use a scalar function as default value of a column. Within this function you can use SYSTEM_USER to get your own user id.
Greets
Flo
April 13, 2009 at 7:40 am
Not quite sure how to go about the suggestions. To say I am a novice is an understatement. Here is the table I put together. How do I implement the suggestions into my table?
CREATE TABLE PART
(
[CREATE_DATE] DATETIME NOT NULL,
[PART_ID] VARCHAR(30) PRIMARY KEY NOT NULL,
[DESCRIPTION] VARCHAR(40) NOT NULL,
[CUST_PART_ID] VARCHAR(30) NULL,
[VOLTAGE] VARCHAR(7) NULL,
[CURRENT] VARCHAR(7) NULL,
[MOD] VARCHAR(1) NULL
)
April 13, 2009 at 7:53 am
Hi
You can specify DEFAULT values for table columns and you can define a scalar function as DEFAULT value of a column. You can create a table with your users and use a scalar function to get your current user id and write it into the column as DEFAULT value.
Try this:
USE tempdb
GO
IF (OBJECT_ID('MyUsers') IS NOT NULL)
DROP TABLE MyUsers
IF (OBJECT_ID('Part') IS NOT NULL)
DROP TABLE Part
IF (OBJECT_ID('ufn_GetCurrentUserId') IS NOT NULL)
DROP FUNCTION ufn_GetCurrentUserId
GO
CREATE TABLE MyUsers
(
Id INT IDENTITY,
Name NVARCHAR(128)
)
GO
CREATE FUNCTION dbo.ufn_GetCurrentUserId ()
RETURNS INT
AS
BEGIN
DECLARE @Id INT
SELECT @Id = Id FROM MyUsers WHERE Name = SYSTEM_USER
RETURN @Id
END
GO
CREATE TABLE Part
(
Id INT IDENTITY,
CreationDate DATETIME DEFAULT GETDATE(),
CreationUser INT DEFAULT dbo.ufn_GetCurrentUserId(),
Description VARCHAR(30)
)
GO
INSERT INTO MyUsers
SELECT SYSTEM_USER
INSERT INTO Part (Description)
SELECT 'Hello World'
SELECT *
FROM Part p
JOIN MyUsers u ON p.CreationUser = u.Id
Greets
Flo
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply