none
when was a table updated? RRS feed

  • Question



  • is there any way to know when was the table last touch (updated) in all scenarios (insert, delete, update, etc..)?
    Tuesday, January 22, 2008 2:17 PM

All replies

  • Jassmin,

    Unfortunately unless there is a trigger or some type of procedure built in that maintains another table used to maintain changes then this information is not available.

     

    Best of luck

    Tuesday, January 22, 2008 2:29 PM
    Moderator
  • then how can i create a trigger for all tables in a database to get the last update, insert, delete or any other kind of changes?


    Wednesday, January 23, 2008 1:57 PM
  • The code below will create an audit table that records the table that was affected, the action taken, insert, update, or delete, the user making the change, and the date and time the change was made.  The code below then uses a string literal along with querying the information_schema.tables view to create t-sql syntax to create the trigger on all tables.  You can then copy the result set into the query window and execute these to create the trigger on all tables in the database.

     

    Good luck!

    Code Snippet

    /*Create the table that will log the actions*/

    If EXISTS

    (

    SELECT *

    FROM dbo.sysobjects

    WHERE id = object_id(N'dbo.AuditTrail')

    AND

    OBJECTPROPERTY(id, N'IsUserTable') = 1

    )

    DROP TABLE dbo.AuditTrail

    GO

    CREATE TABLE dbo.AuditTrail

    (

    AuditTrailID Int IDENTITY (1, 1) NOT NULL,

    TableName VarChar (50) NOT NULL, ActionTaken Char (1)

    NOT NULL, ActionUser VarChar (50) NOT NULL, ActionDate

    DateTime NOT NULL

    )

    /*Use string literal along with the TABLE_NAME

    from the INFORMATION_SCHEMA.TABLES view to create

    an audit trigger on all BASE_TABLES in the

    database*/

    SELECT 'CREATE TRIGGER [AuditInsertUpdate] ON ' +TABLE_NAME +'

    FOR INSERT, UPDATE, DELETE

    AS

    DECLARE @action AS CHAR(1)

    IF (SELECT COUNT(*) FROM INSERTED) = 0

    BEGIN

    SET @action = ''D''

    END

    IF (SELECT COUNT(*) FROM DELETED) = 0

    BEGIN

    SET @action = ''I''

    END

    ELSE

    SET @action = ''U''

    INSERT INTO AuditTrail (TableName, ActionTaken, ActionUser, ActionDate)

    VALUES ('''+ TABLE_NAME +''', @action, User_Name(), GetDate())'

    FROM [INFORMATION_SCHEMA].TABLES

    WHERE TABLE_TYPE = 'BASE TABLE'

     

     

    SINGLE RESULT, cut and paste all results into query window and execute

     

     

    CREATE TRIGGER [AuditInsertUpdate] ON Address    FOR INSERT, UPDATE, DELETE    AS    DECLARE @action AS CHAR(1)    IF (SELECT COUNT(*) FROM INSERTED) = 0    BEGIN    SET @action = 'D'    END  IF (SELECT COUNT(*) FROM DELETED) = 0    BEGIN    SET @action = 'I'    END    ELSE     SET @action = 'U'  INSERT INTO AuditTrail (TableName, ActionTaken,     ActionUser, ActionDate)      VALUES ('Address', @action, User_Name(), GetDate())

    Wednesday, January 23, 2008 2:30 PM
    Moderator
  • Hi,

    I dont agree to add a Trigger to the table, coz it will create performance issues. What you can do is Add 4 columns.

    CREATED_BY,CREATED_DT,MODIFIED_BY,MODIFIED_DT. When new record is inserted the CREATED_DT,MODIFIED-DT is same. If somebody updates the record change the MODIFIED_BY,MODIFIED_DT.

    Now in your requirement you said you want to see the DELETEs performed on the table. Please tell me when you issue DELETE command on table are you doing Hard delete or Soft delete.

    The above mechanism is very standard design in OLTP systems.

     

    Thanks -- Vj

     

    Wednesday, January 23, 2008 5:33 PM