Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: Timestamp Date type

    I have a timestamp data type in my database. I thought it is supposed to get the current date when a field is updated or inserted. However I can't see the value of it after I insert or update a row.
    Do you know why?

    Also, When I have a form with a record source based on a join query, and one of the tables has a timestamp field when inserting in the form I get the following eror:

    "Cannot insert a non null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the time stamp column"

    I don't understand!!

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Timestamp is database-unique incremental counter of values 0..2^64 in MSSQLSERVER2K.
    This value is calculated by system, so you cannot pass it to insert, except NULL value (if the column is nullable).


    Times of just

    insert YourTable1
    select * from YourTable2

    are gone


    Now you should use

    insert YourTable1(YourCol1,YourCol2)
    select YourCol1,YourCol2 from YourTable2

  3. #3
    Join Date
    Jan 2003
    Posts
    8

    Re: Timestamp Date type

    hi

    Inside your INSERT or UPDATE statment may be you can try to use getdate(). I used this with mine and it's working.

    hope this can help you.

    shobapond


    Originally posted by Sia
    I have a timestamp data type in my database. I thought it is supposed to get the current date when a field is updated or inserted. However I can't see the value of it after I insert or update a row.
    Do you know why?

    Also, When I have a form with a record source based on a join query, and one of the tables has a timestamp field when inserting in the form I get the following eror:

    "Cannot insert a non null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the time stamp column"

    I don't understand!!

  4. #4
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Viewing timestamp values

    So there is no way to view the a timestamp value field? Only the system can read it?

    Because when I look at the table all I see is <binary>


    Originally posted by ispaleny
    Timestamp is database-unique incremental counter of values 0..2^64 in MSSQLSERVER2K.
    This value is calculated by system, so you cannot pass it to insert, except NULL value (if the column is nullable).


    Times of just

    insert YourTable1
    select * from YourTable2

    are gone


    Now you should use

    insert YourTable1(YourCol1,YourCol2)
    select YourCol1,YourCol2 from YourTable2

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Sia, I don't find the timestamp datatype very usefull as I often want to know when a record was last modified and by who. I have included some code to illistrate this maybe you will find it of interest:

    Code:
    -- Create table with defaults for audit attributes
    create table Temp(
      KeyField    int not null identity(1,1)
    , DataField   varchar(5) not null
    , LastChanged datetime null CONSTRAINT DF_Temp_LastChange default CURRENT_TIMESTAMP
    , ChangedBy   varchar(30) null CONSTRAINT DF_Temp_ChangedBy default SYSTEM_USER
    )
    
    -- Insert some data
    -- Note that nothing is supplied for the KeyField, LastChanged or ChangedBy attributes
    insert into Temp (DataField) values('A')
    insert into Temp (DataField) values('AB')
    insert into Temp (DataField) values('ABC')
    insert into Temp (DataField) values('ABCD')
    insert into Temp (DataField) values('ABCDE')
    
    -- Insert more data
    -- Note that data is not supplied for the KeyField Attribute, but 
    -- is supplied for the audit attributes.
    insert into Temp (DataField,LastChanged) values('B','01-Jan-1753')
    insert into Temp (DataField,ChangedBy) values('BC','Donald Duck')
    
    -- Take a look
    select * from Temp
    
    go
    
    -- All well and good but if you are trying to keep a tight control on 
    -- the LastChanged or ChangedBy attributes you need a trigger.
    --
    -- Note that with this trigger the two DEFAULT constraints are no longer needed.
    --
    
    -- Drop Default Constraints
    Alter Table Temp drop 
      constraint DF_Temp_LastChange,
      constraint DF_Temp_ChangedBy
    go
    
    -- Now add a trigger to enforce audit fields.
    create trigger Temp_IU on Temp for Insert, Update
    
    as
    
    Update t
       set LastChanged = getdate()
         , ChangedBy = SYSTEM_USER
      from Temp t
      join inserted i on t.KeyField = i.KeyField
    go
    
    -- 5 second delay
    waitfor  delay '00:00:05'
    
    -- Insert some data
    insert into Temp (DataField,LastChanged,ChangedBy) values('BCD','03-Mar-1753','Mickey Mouse')
    insert into Temp (DataField,LastChanged,ChangedBy) values('BCDE','04-Apr-1753','Donald Duck')
    
    -- Take a look
    select * from Temp
    
    -- 5 second delay
    waitfor  delay '00:00:05'
    
    -- Change some data
    update Temp set DataField = DataField where LastChanged = '01-Jan-1753'
    update Temp set DataField = DataField where ChangedBy   = 'Donald Duck'
    
    -- Take a look
    select * from Temp
    
    -- Clean Up
    Drop Trigger Temp_IU
    Drop Table Temp
    go
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks Paul
    Originally posted by Paul Young
    Sia, I don't find the timestamp datatype very usefull as I often want to know when a record was last modified and by who. I have included some code to illistrate this maybe you will find it of interest:

    Code:
    -- Create table with defaults for audit attributes
    create table Temp(
      KeyField    int not null identity(1,1)
    , DataField   varchar(5) not null
    , LastChanged datetime null CONSTRAINT DF_Temp_LastChange default CURRENT_TIMESTAMP
    , ChangedBy   varchar(30) null CONSTRAINT DF_Temp_ChangedBy default SYSTEM_USER
    )
    
    -- Insert some data
    -- Note that nothing is supplied for the KeyField, LastChanged or ChangedBy attributes
    insert into Temp (DataField) values('A')
    insert into Temp (DataField) values('AB')
    insert into Temp (DataField) values('ABC')
    insert into Temp (DataField) values('ABCD')
    insert into Temp (DataField) values('ABCDE')
    
    -- Insert more data
    -- Note that data is not supplied for the KeyField Attribute, but 
    -- is supplied for the audit attributes.
    insert into Temp (DataField,LastChanged) values('B','01-Jan-1753')
    insert into Temp (DataField,ChangedBy) values('BC','Donald Duck')
    
    -- Take a look
    select * from Temp
    
    go
    
    -- All well and good but if you are trying to keep a tight control on 
    -- the LastChanged or ChangedBy attributes you need a trigger.
    --
    -- Note that with this trigger the two DEFAULT constraints are no longer needed.
    --
    
    -- Drop Default Constraints
    Alter Table Temp drop 
      constraint DF_Temp_LastChange,
      constraint DF_Temp_ChangedBy
    go
    
    -- Now add a trigger to enforce audit fields.
    create trigger Temp_IU on Temp for Insert, Update
    
    as
    
    Update t
       set LastChanged = getdate()
         , ChangedBy = SYSTEM_USER
      from Temp t
      join inserted i on t.KeyField = i.KeyField
    go
    
    -- 5 second delay
    waitfor  delay '00:00:05'
    
    -- Insert some data
    insert into Temp (DataField,LastChanged,ChangedBy) values('BCD','03-Mar-1753','Mickey Mouse')
    insert into Temp (DataField,LastChanged,ChangedBy) values('BCDE','04-Apr-1753','Donald Duck')
    
    -- Take a look
    select * from Temp
    
    -- 5 second delay
    waitfor  delay '00:00:05'
    
    -- Change some data
    update Temp set DataField = DataField where LastChanged = '01-Jan-1753'
    update Temp set DataField = DataField where ChangedBy   = 'Donald Duck'
    
    -- Take a look
    select * from Temp
    
    -- Clean Up
    Drop Trigger Temp_IU
    Drop Table Temp
    go

  7. #7
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks Paul. Your code was very usfull for me and I learned a lot. I just don't understand this part:
    ------------------------------
    create trigger Temp_IU on Temp for Insert, Update

    as

    Update t
    set LastChanged = getdate()
    , ChangedBy = SYSTEM_USER
    from Temp t
    join inserted i on t.KeyField = i.KeyField
    go
    -------------------------------------------------
    I understand the trigger and what it does. I just don't get the join:
    "join inserted i on t.KeyField = i.KeyField"

    What is the join for?

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    the temporary inserted table holds one record for every inserted or updated record in the Temp table. By joining the inserted table and the Temp table I can adjust all records affected by the insert or update.

    I could have also used :
    Code:
    Update Temp
       set LastChanged = getdate()
         , ChangedBy = SYSTEM_USER
     where KeyField in (Select KeyField from inserted)
    Many people think a trigger fires for each modified record, it doesn't. The trigger is fired once for all records affected by the insert or update statment wether it's one or 100 records.

    Clear as Mud? Maybe Microsoft can do a better job... Look up "triggers, multirow" in Books Online.
    Last edited by Paul Young; 02-07-03 at 14:42.
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    So if we want to keep track of who did what in a table, there is no way to, for example, set triggers on specific columns and have the previus and changed data saved in another table (this might be doable using a front end application like access, puting a procedure on "after update" of a field.)

    Can we use stored procedures to track changes in a db?

    Also do you know of any good referrences that discuss tracking changes in a database by users?

    Thanks

  10. #10
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    inserted is not temporary table, try this code to see more

    create table test(X int)
    GO
    create trigger trX on test for insert as
    create index INC_I on inserted(X)
    GO
    insert test(X) values (0)
    insert test(X) values (0)
    GO
    drop table test

  11. #11
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    This is a simple table auditing schema

    Code:
    
    create table dbo.Test
    (
     IdCol     int identity(1,1) primary key
    ,Data      char(1) null
    )
    GO
    
    --Lockdown
    create table dbo.XAUDIT_Test
    (
     ChangeID   int identity(1,1) primary key
    ,OperationType char(1) not null
    ,ChangeTime datetime      default CURRENT_TIMESTAMP
    ,WhoChanged nvarchar(256) default SYSTEM_USER
    ,IdCol      int not null
    ,Data       char(1) null
    )
    GO
    deny all on dbo.XAUDIT_Test to public
    GO
    create trigger ti_Test_Ins on dbo.Test
    for insert as
    insert into dbo.XAUDIT_Test(OperationType,IdCol,Data)
    select 'I',IdCol,Data from inserted
    GO
    create trigger ti_Test_Upd on dbo.Test
    for update as
    insert into dbo.XAUDIT_Test(OperationType,IdCol,Data)
    select 'U',IdCol,Data from inserted
    GO
    create trigger ti_Test_Del on dbo.Test
    for delete as
    insert into dbo.XAUDIT_Test(OperationType,IdCol,Data)
    select 'D',IdCol,Data from deleted
    GO
    
    --test
    insert into dbo.Test(Data) values ('A')
    waitfor delay '00:00:00.05'
    insert into dbo.Test(Data) values ('B')
    -- Resultset 1
    select * from dbo.XAUDIT_Test
    waitfor delay '00:00:00.05'
    update dbo.Test set data='C' where data='A'
    -- Resultset 2
    select * from dbo.XAUDIT_Test
    delete dbo.Test
    -- Resultset 3
    select * from dbo.XAUDIT_Test
    GO
    
    --But dbo can delete all, normal users must have normal accounts
    delete dbo.XAUDIT_Test
    -- Resultset 4
    select * from dbo.XAUDIT_Test
    
    --clean up
    drop table dbo.Test
    drop table dbo.XAUDIT_Test
    

  12. #12
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    isapleny, The inserted and deleted tables are temporary, you might want to read up on them in Books Online, look up "inserted tables" in the index.

    Sia, isapleny has provided a good example of how an audit trail can be setup. What I provided is what you would generaly find in most databases. What isapleny provided is what you would do to track specific tables.

    I would suggest the following changes though:
    Code:
    create table dbo.XAUDIT_Test(
     ChangeID      int identity(1,1) primary key
    ,OperationType varchar(2) not null
    ,ChangeTime    datetime      default CURRENT_TIMESTAMP
    ,WhoChanged    nvarchar(256) default SYSTEM_USER
    ,IdCol         int not null
    ,Data          char(1) null)
    GO
    
    
    create trigger ti_Test_Upd on dbo.Test
    for update as
    insert into dbo.XAUDIT_Test(OperationType,IdCol,Data)
    select 'UO',IdCol,Data from deleted
    insert into dbo.XAUDIT_Test(OperationType,IdCol,Data)
    select 'UN',IdCol,Data from inserted
    GO
    If you wanted to combine all three triggers into one you might use:
    Code:
    create trigger TR_Test_IUD on dbo.Test
    for Insert, Update, Delete
    as
    if exists(select * from inserted)
      if exists(select * from deleted) begin
        -- Update
        insert into dbo.XAUDIT_Test(OperationType,IdCol,Data)
        select 'UO',IdCol,Data from deleted
        insert into dbo.XAUDIT_Test(OperationType,IdCol,Data)
        select 'UN',IdCol,Data from inserted
      end else
        -- Insert
        insert into dbo.XAUDIT_Test(OperationType,IdCol,Data)
        select 'I',IdCol,Data from inserted
    else
      -- Insert
      insert into dbo.XAUDIT_Test(OperationType,IdCol,Data)
      select 'D',IdCol,Data from deleted
    isapleny's solution uses simple triggers and is faster to execute where as my suggestion leans more towards maintainability. Both would work just fine.

    You can't set triggers on specific columns but you can test to see which columns have been alterd. Books Online has an excellent example of testing for changes to one or more attributes look up "create trigger" in the index. Look it over and post back with specific questions.
    Last edited by Paul Young; 02-10-03 at 08:55.
    Paul Young
    (Knowledge is power! Get some!)

  13. #13
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    What do you think?

    Thanks for all your help guys, I've almost solved my problem.
    My last issue (I hope its my last !) is how to perform the tables audit on a field level instead of a row level.

    I was thinking of having a general "History" or "Audit" table, where it will have for example the following fields:

    (ChangeID, Table_Name, FieldName,Initial-Value,NewValue)

    When a user updates a field, a triggers fires and inserts the tablename (or table ID from sysobjects) the field that was changed and the initial and new value. This will save a lot of storage and time and will be much easier to maintain, however it might by a little harder to query. What do you guys think?

    Thanks

  14. #14
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Not a bad idea but I think the overhead of supporting this in a trigger might not be worth the effort. You will need code to detect which attributes have changed, could be more than one, and then insert each change into your History table. If three attributes changed in 20 records then you will be inserting 60 records into your history table via three insert/selects pluse your user will need to wait for all this to happen. I can't say if this is a good or bad thing in your environment but it is something that should be considered.

    Another approach I have seen is to have a generic table with a structure of:

    ChangeDate, TableName, Modification, Data

    where "Data" holds the modified record cast to a varchar. A big problem with this is the record is no longer easily searchable.

    I have been wondering if this approach would be doable by storing the record as XML?
    Paul Young
    (Knowledge is power! Get some!)

  15. #15
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    I don't have experience with XML.
    But about your comment regarding the overhead I have to agree with you.



    Originally posted by Paul Young
    Not a bad idea but I think the overhead of supporting this in a trigger might not be worth the effort. You will need code to detect which attributes have changed, could be more than one, and then insert each change into your History table. If three attributes changed in 20 records then you will be inserting 60 records into your history table via three insert/selects pluse your user will need to wait for all this to happen. I can't say if this is a good or bad thing in your environment but it is something that should be considered.

    Another approach I have seen is to have a generic table with a structure of:

    ChangeDate, TableName, Modification, Data

    where "Data" holds the modified record cast to a varchar. A big problem with this is the record is no longer easily searchable.

    I have been wondering if this approach would be doable by storing the record as XML?

Posting Permissions

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