Finalizing a field

Hello all

I have SQL Server 2005 in as the engine and Access 2003 on the front end. And I was wondering if the SQL has a feature where you can allow users to update a field but once its updated they can not edit it. Once the form has been filled out they cant make any changes to it without a supervisor logging in. Does that make sense?? Is this possible??there is no write - once unless I am an admin feature I know of. This you would have to handle in the application code for the interface.|||Dear my Desire,

You would need to have a column that has an indicator or a datetime filed that indactes that the row has been updated.

The front end would then interogate that field.

If it exists, then you need to "protect" the field from updates.

From a backend perspective, you could write a trigger to do the same thing, and rollback the trans if an update is trying to occur against a row that's already been modified.

BUT

This makes no sense|||I'm just wondering; should the question be

Can I allow all users to INSERT and only allow supervisors to UPDATE?|||Dear my Desire,

You would need to have a column that has an indicator or a datetime filed that indactes that the row has been updated.

The front end would then interogate that field.

If it exists, then you need to "protect" the field from updates.

From a backend perspective, you could write a trigger to do the same thing, and rollback the trans if an update is trying to occur against a row that's already been modified.

BUT

This makes no sense

I was just curious guys, the thing is supervisors dont want users to be able to edit anyones data in the database once its been entered. they dont want anyone manipulating the system, does that make any sense??|||...ok, let's go out on a limb here, and say that the users may be falable

What if the data is enetered incorrectly

Can't they fix it?

In any case, I would use a history tabel and a trigger to track all data changes|||I'm arriving late to this party, but I'm 100% with Brett on this. Assuming that users will always get data right on the first try is a great party joke, but not appropriate until after you've had enough drinks that you aren't able to dance on the table even with your shoes off.

-PatP|||Thank you guys, we are going to use a history table and track all the changes that are being done, because thats just ridiculous. A history table would make allot more sense. If I can get sql to track when they login, when they insert, update and modify the data, like a creation date and a modified date..etc. That makes allot more sense. thanks guys I know that was a stupid question, but how do you know if you dont ask|||Do you need some help with that?

Don't store the inserts in history, just leave them in the base table

Also, how does the application login to the database?|||THANK YOU SO MUCH,

I wanted to create a history table that will link to the main table. and in this history table I wanted sql to keep track of the date they logged in, the date they modified, updated and deleted data.

Am I on the right track??|||Generates History tables

SET NOCOUNT ON
GO
/*
-- CREATE A Driver Table for all Tables in your catalog you wish to audit
CREATE TABLE myAudit99(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname)

-- Populate the audit driver table with the table you want...you can use any type of process for this

TRUNCATE TABLE myAudit99

INSERT INTO myAudit99(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.Tables
WHERE 1=1
AND TABLE_NAME NOT LIKE '%_H'
AND TABLE_NAME NOT LIKE 'v_%'
AND TABLE_NAME <> 'dtproperties'
AND TABLE_NAME <> 'myAudit99'
AND TABLE_NAME <> 'myColumns45'
AND TABLE_NAME <> 'myColumns45'
AND TABLE_NAME <> 'MySprocs99'
AND TABLE_NAME <> 'MyStage99'
AND TABLE_NAME <> 'myTables45'
AND TABLE_NAME <> 'myTableSearch99'
AND TABLE_NAME <> 'MyWork99'
AND TABLE_NAME <> 'sysconstraints'
AND TABLE_NAME <> 'syssegments'

SELECT * FROM INFORMATION_SCHEMA.Columns
WHERE CHARACTER_MAXIMUM_LENGTH > 6000

*/
-- Lets create some audit tables based on the Driver

DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM myAudit99
-- WHERE TABLE_NAME <> 'myAudit99'
WHERE TABLE_NAME <> 'MEP'

DECLARE @.TABLE_CATALOG sysname, @.TABLE_SCHEMA sysname, @.TABLE_NAME sysname, @.COLUMN_NAMES varchar(8000)
, @.sql varchar(8000), @.drop varchar(8000)
SELECT @.COLUMN_NAMES = ''
OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @.TABLE_CATALOG, @.TABLE_SCHEMA, @.TABLE_NAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL = 'CREATE TABLE ' + @.TABLE_CATALOG + '.' + @.TABLE_SCHEMA + '.' + @.TABLE_NAME + '_H ('
+ ' HIST_ADD_DT datetime DEFAULT (getDate()), HIST_ADD_TYPE char(1) NOT NULL'
+ ', HIST_ADD_SYSTEM_USER sysname NOT NULL, HIST_ADD_USER_NAME sysname NOT NULL'
+ ', HIST_ADD_HOSTNAME sysname NOT NULL, HIST_ADD_SPID int NOT NULL, HIST_ADD_DESC varchar(50) '
, @.COLUMN_NAMES = @.COLUMN_NAMES + ', ' + COLUMN_NAME
+ ' ' + DATA_TYPE
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(varchar(20),CHARACTER_MAXIMUM_LENGTH) + ')'
WHEN DATA_TYPE = 'decimal' THEN '(' + CONVERT(varchar(20),NUMERIC_PRECISION) + ','
+ CONVERT(varchar(20),NUMERIC_SCALE) + ')'
ELSE ''
END
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = @.TABLE_CATALOG AND TABLE_SCHEMA = @.TABLE_SCHEMA AND TABLE_NAME = @.TABLE_NAME
ORDER BY ORDINAL_POSITION

SELECT @.SQL = @.SQL + @.COLUMN_NAMES + ')'

SELECT @.drop =
'if exists (select * from dbo.sysobjects where id = object_id(N'
+''''+'['+@.TABLE_SCHEMA+'].['+@.TABLE_NAME+'_H]'
+''''+') and OBJECTPROPERTY(id, N'
+''''+'IsUserTable'
+''''+') = 1)'+CHAR(13)+CHAR(10)
+'drop table ['+@.TABLE_SCHEMA+'].['+@.TABLE_NAME+'_H]'

--SELECT @.DROP
EXEC(@.drop)

-- EXEC(@.SQL)
--SELECT @.sql

SELECT @.SQL = '', @.COLUMN_NAMES = ''

FETCH NEXT FROM myCursor99 INTO @.TABLE_CATALOG, @.TABLE_SCHEMA, @.TABLE_NAME

END

CLOSE myCursor99
DEALLOCATE myCursor99
GO|||Generates Triggers

DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
--FROM INFORMATION_SCHEMA.Tables -- To wipe out all history cursors
FROM myAudit99
WHERE 1=1
--AND TABLE_NAME <> 'myAudit99'
AND TABLE_NAME <> 'MEP'
--AND TABLE_NAME NOT IN ('MySprocs99','Folder', 'dtproperties')

DECLARE @.TABLE_CATALOG sysname, @.TABLE_SCHEMA sysname, @.TABLE_NAME sysname, @.COLUMN_NAMES varchar(8000), @.sql varchar(8000)

DECLARE @.DROP varchar(8000)

SELECT @.COLUMN_NAMES = ''
OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @.TABLE_CATALOG, @.TABLE_SCHEMA, @.TABLE_NAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.COLUMN_NAMES = @.COLUMN_NAMES + ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = @.TABLE_CATALOG AND TABLE_SCHEMA = @.TABLE_SCHEMA AND TABLE_NAME = @.TABLE_NAME
ORDER BY ORDINAL_POSITION

SELECT @.SQL = 'CREATE TRIGGER ' + @.TABLE_SCHEMA + '_' + @.TABLE_NAME + '_TR ON ' + @.TABLE_SCHEMA + '.' +@.TABLE_NAME
+ ' FOR UPDATE, DELETE AS SET NOCOUNT ON ' + CHAR(13) + CHAR(10)
+ ' DECLARE @.HOSTNAME sysname, @.DESC varchar(50) ' + CHAR(13) + CHAR(10)
+ ' SELECT @.HOSTNAME = hostname from master.dbo.sysprocesses where spid = @.@.SPID ' + CHAR(13) + CHAR(10)
+ ' IF EXISTS(SELECT * FROM ' + @.TABLE_NAME + ') SELECT @.DESC = '
+ '''' + '''' + ' ELSE SELECT @.DESC = ' + '''' + 'MASS DELETE' + '''' + CHAR(13) + CHAR(10)
+ ' If Exists (Select * From Inserted) And Exists (Select * From Deleted) '
+ ' INSERT INTO ' + @.TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @.COLUMN_NAMES + ')' + CHAR(13) + CHAR(10)
+ ' SELECT ''U'', SYSTEM_USER, USER_NAME(), @.HOSTNAME, @.@.SPID, @.DESC'
+ @.COLUMN_NAMES + ' FROM deleted' + CHAR(13) + CHAR(10)
+ ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted) And @.DESC = '''' '
+ ' INSERT INTO ' + @.TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @.COLUMN_NAMES + ')' + CHAR(13) + CHAR(10)
+ ' SELECT ''D'', SYSTEM_USER, USER_NAME(), @.HOSTNAME, @.@.SPID, @.DESC'
+ @.COLUMN_NAMES + ' FROM deleted' + CHAR(13) + CHAR(10)
+ ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted) And @.DESC <> '''' ' + CHAR(13) + CHAR(10)
+ ' INSERT INTO ' + @.TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @.COLUMN_NAMES + ')' + CHAR(13) + CHAR(10)
+ ' SELECT TOP 1 ''D'', SYSTEM_USER, USER_NAME(), @.HOSTNAME, @.@.SPID, @.DESC'
+ @.COLUMN_NAMES + ' FROM deleted'


SELECT @.DROP = 'if exists (select * from dbo.sysobjects where id = object_id(N'
+ ''''+'[dbo].'
+ '[' + @.TABLE_SCHEMA + '_' + @.TABLE_NAME + '_TR]'
+ ''''
+') and OBJECTPROPERTY(id, N'
+ ''''
+'IsTrigger'
+ ''''
+') = 1)'+ CHAR(13) + CHAR(10)
+ ' drop trigger [dbo].'
+ '[' + @.TABLE_SCHEMA + '_' + @.TABLE_NAME + '_TR]'

--SELECT @.DROP
EXEC(@.DROP)

-- EXEC('DROP TRIGGER ' + '[' + @.TABLE_SCHEMA + '_' + @.TABLE_NAME + '_TR]')

--EXEC(@.SQL)

--SELECT @.sql

SELECT @.SQL = '', @.COLUMN_NAMES = ''

FETCH NEXT FROM myCursor99 INTO @.TABLE_CATALOG, @.TABLE_SCHEMA, @.TABLE_NAME

END

CLOSE myCursor99
DEALLOCATE myCursor99
GO|||they want fields audited as well as the entire table. for example if someone enters data into a field, closes out of the database then goes back in and edits that field they want an indicator to tell them when and what time did they do that. Does that make sense? I was trying to find a date function I could use to do that