Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: 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??

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    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.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm just wondering; should the question be
    Can I allow all users to INSERT and only allow supervisors to UPDATE?
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by Brett Kaiser
    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??

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ...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
    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.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

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

    I just had a meeting

    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

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    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.

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

    You Guys Are So Awesome

    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??

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Generates History tables

    Code:
    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
    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.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Generates Triggers

    Code:
    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
    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.

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

    another meeting

    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

Posting Permissions

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