locked
sql server security (Auditing) RRS feed

  • Question

  • Hi There,

    Im trying to create a trigger for all tables to audit them. but I get the error message

    Msg 8197, Level 16, State 4, Procedure customers_usage_tr, Line 1

    The object 'customers' does not exist or is invalid for this operation.

     

    * the code:

    GO

    CREATE TABLE LOG_TABLE (Add_dttm datetime DEFAULT (GetDate()), TABLE_NAME sysname, Activity char(6))
    GO

    DECLARE @sql varchar(8000), @TABLE_NAME sysname
    SET NOCOUNT ON

    SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables

    WHILE @TABLE_NAME IS NOT NULL
      BEGIN
     SELECT @sql = 'CREATE TRIGGER [' + @TABLE_NAME + '_Usage_TR] ON [' + @TABLE_NAME +'] '
      + 'FOR INSERT, UPDATE, DELETE AS '
      + 'IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) '
      + 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''INSERT''' + ' '
      + 'IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) '
      + 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''UPDATE''' + ' '
      + 'IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) '
      + 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''DELETE''' + ' GO'
     SELECT @sql
     EXEC(@sql)
     SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME > @TABLE_NAME 
      END
    SET NOCOUNT OFF

     


    Alanazi
    Monday, December 12, 2011 10:17 AM

Answers

  • Hi Alanazi,

    Please declare another variable, such as the statement below:


    DECLARE @SCH_Name varchar(200)
    SELECT @SCH_Name=TABLE_SCHEMA+'.'+TABLE_NAME  FROM INFORMATION_SCHEMA.Tables


    Use @SCH_Name instead of @TABLE_NAME in your code, if necessary.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Stephanie Lv Tuesday, December 20, 2011 10:33 AM
    Wednesday, December 14, 2011 8:29 AM

All replies

  • What version of SQL Server are you using? You know, since SQL Server 2008 there are built in functions for auditing
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, December 12, 2011 10:19 AM
  • Looks like Customers table  does not in dbo schema?

    http://uk.linkedin.com/in/ramjaddu
    Monday, December 12, 2011 10:45 AM
  • I use Sql Server 2008, could you please show me where I can found this build in function for auditing.
    Alanazi
    Tuesday, December 13, 2011 3:58 AM
  • Customers table is in CustomerDetails schema. Actually, I thing the problem is the code above does not detemine the schema, but how I can modify the code to specify the schema.

    Thanks


    Alanazi
    Tuesday, December 13, 2011 4:02 AM
  • Hi Alanazi,

    Please declare another variable, such as the statement below:


    DECLARE @SCH_Name varchar(200)
    SELECT @SCH_Name=TABLE_SCHEMA+'.'+TABLE_NAME  FROM INFORMATION_SCHEMA.Tables


    Use @SCH_Name instead of @TABLE_NAME in your code, if necessary.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Stephanie Lv Tuesday, December 20, 2011 10:33 AM
    Wednesday, December 14, 2011 8:29 AM