Results 1 to 8 of 8

Thread: Log Ins

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Log Ins

    Hi all I was wondering how would I keep tabs on when users enter data in a database? Can I create a field or program a table to keep track of the users that enter in data so theres no mix up, to automatically keep track of users entering in data

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is a sample script:
    Code:
    CREATE TABLE [Sample]
    	([SampleID] [uniqueidentifier] NOT NULL,
    	[Sample] [varchar] (50) NOT NULL,
    	[Modified] [datetime] NULL,
    	[Modifier] [varchar] (100) NULL)
    	ALTER TABLE [dbo].[Sample] WITH NOCHECK ADD
    	CONSTRAINT [PK_Sample] PRIMARY KEY  CLUSTERED ([SampleID])
    
    ALTER TABLE [dbo].[Sample] ADD CONSTRAINT [DF_Sample_SampleID] DEFAULT (newid()) FOR [SampleID]
    ALTER TABLE [dbo].[Sample] ADD CONSTRAINT IX_Sample UNIQUE NONCLUSTERED ([Sample]) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    ALTER TABLE [dbo].[Sample] ADD CONSTRAINT [DF_Sample_Modified] DEFAULT (getdate()) FOR [Modified]
    ALTER TABLE [dbo].[Sample] ADD CONSTRAINT [DF_Sample_Modifier] DEFAULT (convert(varchar(100), host_name() + ':' + suser_sname() + '(' + current_user + ') - ' + app_name())) FOR [Modifier]
    GO
    
    CREATE TRIGGER TR_Sample_U ON [dbo].[Sample] 
    FOR UPDATE
    AS
    set nocount on
    update	[Sample]
    set	Modified = getdate(),
    	Modifier = convert(varchar(100), host_name() + ':' + Original_Login() + '(' + current_user + ') - ' + app_name())
    from	[Sample]
    	inner join inserted on [Sample].[SampleID] = inserted.[SampleID]
    GO
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Hi Blindman

    How are you these days? Thank you for your reply, I give it a shot

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm doing fine thank you.

    Try this script. Just enter your table name in the appropriate variable and indicated whether you want to use GUIDs or Identities for a surrogate key, and execute the script in text mode. It will write a script that you can copy and paste to create your new table.

    Code:
    set nocount on
    declare	@TableName varchar(50)
    declare	@UseGUIDs varchar(4)
    set	@TableName = 'SampleRecords'
    set	@UseGUIDs = 'Y'  --'Y' for GUIDs, 'N' for Identity
    
    declare	@EntityName varchar(50)
    declare	@IDName varchar(50)
    declare	@Version char(4)
    
    set	@Version = substring(@@Version, 22, 4)
    
    if	right(@TableName, 3) = 'ies' set @EntityName = left(@TableName, len(@TableName)-3) + 'y'
    else if	right(@TableName, 4) = 'sses' set @EntityName = left(@TableName, len(@TableName)-2)
    else if	right(@TableName, 6) = 'status' set @EntityName = @TableName
    else if	right(@TableName, 8) = 'statuses' set @EntityName = left(@TableName, len(@TableName)-2)
    else if	right(@TableName, 1) = 's' set @EntityName = left(@TableName, len(@TableName)-1)
    	else set @EntityName = @TableName
    
    if	right(@TableName, 3) = 'ies' set @IDName = left(@TableName, len(@TableName)-3) + 'yID'
    else if	right(@TableName, 4) = 'sses' set @IDName = left(@TableName, len(@TableName)-2) + 'ID'
    else if	right(@TableName, 6) = 'status' set @IDName = @TableName + 'ID'
    else if	right(@TableName, 8) = 'statuses' set @IDName = left(@TableName, len(@TableName)-2) + 'ID'
    else if	right(@TableName, 1) = 's' set @IDName = left(@TableName, len(@TableName)-1) + 'ID'
    	else set @IDName = @TableName + 'ID'
    
    declare	@SQLString varchar(8000)
    set	@SQLString = 
    'Use ' + case when db_name() in ('master', 'msdb', 'model') then '[XXX]' else db_name() end + '
    ' + replace('GXO' ,'X', '') + '
    
    CREATE TABLE [' + @TableName + ']
    	([' + @IDName + '] ' + Case when @UseGUIDs = 'Y' then '[uniqueidentifier] NOT NULL' else '[bigint] IDENTITY(1,1) NOT NULL' end + ',
    	[' + @EntityName + '] [varchar] (50) NOT NULL,
    	[Modified] [datetime] NULL,
    	[Modifier] [varchar] (100) NULL)
    	ALTER TABLE [dbo].[' + @TableName + '] WITH NOCHECK ADD
    	CONSTRAINT [PK_' + @TableName + '] PRIMARY KEY  CLUSTERED ([' + @IDName + '])
    
    ' + Case when @UseGUIDs = 'Y' then 'ALTER TABLE [dbo].[' + @TableName + '] ADD CONSTRAINT [DF_' + @TableName + '_' + @IDName + '] DEFAULT (newid()) FOR [' + @IDName + ']' else '' end + '
    ALTER TABLE [dbo].[' + @TableName + '] ADD CONSTRAINT IX_' + @TableName + ' UNIQUE NONCLUSTERED ([' + @EntityName + '])
    ALTER TABLE [dbo].[' + @TableName + '] ADD CONSTRAINT [DF_' + @TableName + '_Modified] DEFAULT (getdate()) FOR [Modified]
    ALTER TABLE [dbo].[' + @TableName + '] ADD CONSTRAINT [DF_' + @TableName + '_Modifier] DEFAULT (convert(varchar(100), host_name() + '':'' + suser_sname() + ''('' + current_user + '') - '' + app_name())) FOR [Modifier]
    ' + replace('GXO' ,'X', '') + '
    
    CREATE TRIGGER TR_' + @TableName + '_U ON [dbo].[' + @TableName + '] 
    FOR UPDATE
    AS
    set nocount on
    update	[' + @TableName + ']
    set	Modified = getdate(),
    ' + case when @Version >= '2005'
    	then '	Modifier = convert(varchar(100), host_name() + '':'' + Original_Login() + ''('' + current_user + '') - '' + app_name())'
    	else '	Modifier = convert(varchar(100), host_name() + '':'' + suser_sname() + ''('' + current_user + '') - '' + app_name())'
        end  + '
    from	[' + @TableName + ']
    	inner join inserted on [' + @TableName + '].[' + @IDName + '] = inserted.[' + @IDName + ']
    ' + replace('GXO' ,'X', '') + ''
    
    select	@SQLString
    See how lazy I am?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by desireemm
    Hi all I was wondering how would I keep tabs on when users enter data in a database? Can I create a field or program a table to keep track of the users that enter in data so theres no mix up, to automatically keep track of users entering in data
    Do you have sproc access only?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Thank you So much

    Quote Originally Posted by blindman
    I'm doing fine thank you.

    Try this script. Just enter your table name in the appropriate variable and indicated whether you want to use GUIDs or Identities for a surrogate key, and execute the script in text mode. It will write a script that you can copy and paste to create your new table.

    Code:
    set nocount on
    declare	@TableName varchar(50)
    declare	@UseGUIDs varchar(4)
    set	@TableName = 'SampleRecords'
    set	@UseGUIDs = 'Y'  --'Y' for GUIDs, 'N' for Identity
    
    declare	@EntityName varchar(50)
    declare	@IDName varchar(50)
    declare	@Version char(4)
    
    set	@Version = substring(@@Version, 22, 4)
    
    if	right(@TableName, 3) = 'ies' set @EntityName = left(@TableName, len(@TableName)-3) + 'y'
    else if	right(@TableName, 4) = 'sses' set @EntityName = left(@TableName, len(@TableName)-2)
    else if	right(@TableName, 6) = 'status' set @EntityName = @TableName
    else if	right(@TableName, 8) = 'statuses' set @EntityName = left(@TableName, len(@TableName)-2)
    else if	right(@TableName, 1) = 's' set @EntityName = left(@TableName, len(@TableName)-1)
    	else set @EntityName = @TableName
    
    if	right(@TableName, 3) = 'ies' set @IDName = left(@TableName, len(@TableName)-3) + 'yID'
    else if	right(@TableName, 4) = 'sses' set @IDName = left(@TableName, len(@TableName)-2) + 'ID'
    else if	right(@TableName, 6) = 'status' set @IDName = @TableName + 'ID'
    else if	right(@TableName, 8) = 'statuses' set @IDName = left(@TableName, len(@TableName)-2) + 'ID'
    else if	right(@TableName, 1) = 's' set @IDName = left(@TableName, len(@TableName)-1) + 'ID'
    	else set @IDName = @TableName + 'ID'
    
    declare	@SQLString varchar(8000)
    set	@SQLString = 
    'Use ' + case when db_name() in ('master', 'msdb', 'model') then '[XXX]' else db_name() end + '
    ' + replace('GXO' ,'X', '') + '
    
    CREATE TABLE [' + @TableName + ']
    	([' + @IDName + '] ' + Case when @UseGUIDs = 'Y' then '[uniqueidentifier] NOT NULL' else '[bigint] IDENTITY(1,1) NOT NULL' end + ',
    	[' + @EntityName + '] [varchar] (50) NOT NULL,
    	[Modified] [datetime] NULL,
    	[Modifier] [varchar] (100) NULL)
    	ALTER TABLE [dbo].[' + @TableName + '] WITH NOCHECK ADD
    	CONSTRAINT [PK_' + @TableName + '] PRIMARY KEY  CLUSTERED ([' + @IDName + '])
    
    ' + Case when @UseGUIDs = 'Y' then 'ALTER TABLE [dbo].[' + @TableName + '] ADD CONSTRAINT [DF_' + @TableName + '_' + @IDName + '] DEFAULT (newid()) FOR [' + @IDName + ']' else '' end + '
    ALTER TABLE [dbo].[' + @TableName + '] ADD CONSTRAINT IX_' + @TableName + ' UNIQUE NONCLUSTERED ([' + @EntityName + '])
    ALTER TABLE [dbo].[' + @TableName + '] ADD CONSTRAINT [DF_' + @TableName + '_Modified] DEFAULT (getdate()) FOR [Modified]
    ALTER TABLE [dbo].[' + @TableName + '] ADD CONSTRAINT [DF_' + @TableName + '_Modifier] DEFAULT (convert(varchar(100), host_name() + '':'' + suser_sname() + ''('' + current_user + '') - '' + app_name())) FOR [Modifier]
    ' + replace('GXO' ,'X', '') + '
    
    CREATE TRIGGER TR_' + @TableName + '_U ON [dbo].[' + @TableName + '] 
    FOR UPDATE
    AS
    set nocount on
    update	[' + @TableName + ']
    set	Modified = getdate(),
    ' + case when @Version >= '2005'
    	then '	Modifier = convert(varchar(100), host_name() + '':'' + Original_Login() + ''('' + current_user + '') - '' + app_name())'
    	else '	Modifier = convert(varchar(100), host_name() + '':'' + suser_sname() + ''('' + current_user + '') - '' + app_name())'
        end  + '
    from	[' + @TableName + ']
    	inner join inserted on [' + @TableName + '].[' + @IDName + '] = inserted.[' + @IDName + ']
    ' + replace('GXO' ,'X', '') + ''
    
    select	@SQLString
    See how lazy I am?

    I needed to figure something out for keeping track of who is entering data and when. I was looking on BOL to see if I can find something, I'm the main DBA so I have full access to sql enterprise 2005 which I am still learning about. But I think I like 2005 better

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Error messages

    Hi Blindman when I run it I get error messages


    Code:
    Msg 102, Level 15, State 1, Line 13
    Incorrect syntax near ')'.
    Msg 105, Level 15, State 1, Line 30
    Unclosed quotation mark after the character string '
    '.
    Msg 102, Level 15, State 1, Line 30
    Incorrect syntax near '
    '.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Make sure your query tool is not truncating the results. You will need to bump up the max resultset characters option to 8000.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •