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 dataHere is a sample script:
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|||How are you these days? Thank you for your reply, I give it a shot|||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.
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?|||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?|||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.
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|||Hi Blindman when I run it I get error messages
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 '
'.|||Make sure your query tool is not truncating the results. You will need to bump up the max resultset characters option to 8000.
No comments:
Post a Comment