Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2008
    Posts
    46

    Unanswered: how do we know db modification date and time

    Hello Everyone,

    Can we know the date of db modification from system table or log? If anyone know pls let us know. On which table we can see.

    eg. any db change or effect on the tables (insert/update/delete)

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not retrospectively unless you are really desperate in which case you can fork out for logreader by lumigent. I've never used it but this is the stock answer to this common question.

    You can log pretty well anything you like but most things are not logged in a human usable form unless specifically set up before hand.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2008
    Posts
    23
    hi rajan 142,
    Triggers will help you achieve this. If you are still looking to solve this, I can help.
    One caveat though. We should do this only on very critical tables/fields. It could become a performance drain, if used without proper planning and insight.
    hope it helps.
    thankz
    jambu

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    You can also use history tables for more detail or date modification fields. Most of my critical entity tables have a date created field that has a default constraint of GETDATE(), so that I can easily track inserts. There are lots of possibilities.
    “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.

  5. #5
    Join Date
    Oct 2008
    Posts
    46
    Pls Explen How?
    Really i need
    Quote Originally Posted by kjambu
    hi rajan 142,
    Triggers will help you achieve this. If you are still looking to solve this, I can help.
    One caveat though. We should do this only on very critical tables/fields. It could become a performance drain, if used without proper planning and insight.
    hope it helps.
    thankz
    jambu

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rajan142
    Pls Explen How?
    Really i need
    This sort of stuff:
    http://www.google.co.uk/search?sourc...e+Search&meta=
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2008
    Posts
    23
    hello rajan142,
    --Small explanation of code.
    --This is a very simple example to show how you can track changes made to a field in your table.
    --Triggers are basically stored procedures, with the primary difference that they execute when a
    -- specific event occurs rather than being explicitly called.
    --I can always help, if you need serious help with such design issues.

    --run this script in QueryAnalyzer
    if object_id('jk_MainTable') is not null
    drop table jk_MainTable
    go
    if object_id('jk_AuditTable') is not null
    drop table jk_AuditTable
    go
    if object_id('tr_jk_MainTable') is not null
    drop trigger tr_jk_MainTable
    go
    create table jk_MainTable(f1 int identity, f2 varchar(100), f3 varchar(100))

    create table jk_AuditTable(f1 int identity, f2 varchar(100), f3 varchar(100),
    ModifiedBy varchar(100), ModifiedDate datetime, jkMainTableID int)

    insert into jk_MainTable values('AAAAA','11111')
    insert into jk_MainTable values('BBBBB','22222')
    insert into jk_MainTable values('CCCCC','33333')
    insert into jk_MainTable values('DDDDD','44444')
    insert into jk_MainTable values('EEEEE','55555')

    go
    create trigger tr_jk_MainTable
    on jk_MainTable
    AFTER UPDATE --INSERT, DELETE
    AS
    BEGIN
    INSERT into jk_AuditTable (f2, f3, ModifiedBy, ModifiedDate,jkMainTableID)
    SELECT d.f2, d.f3, system_user, getdate(), d.f1 from deleted d
    END
    go

    --Then test using this script
    select * from jk_AuditTable
    update jk_MainTable set f3 = '99999' where f2 = 'AAAAA'
    select * from jk_AuditTable
    select * from jk_MainTable

Posting Permissions

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