Create an extra table (for audit purpose)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Create an extra table (for audit purpose)

Author

Topic

sauron
Starting Member

2 Posts

Posted - 2008-03-28 : 03:59:20

Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose.
For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work.
Please advise.. Thanks in advance.

Here is my code:


USE pubs

DECLARE @TABLE_NAME sysname
DECLARE @AUDIT_TABLE VARCHAR(50)

SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME NOT LIKE 'audit%'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME = 'sales'

WHILE @TABLE_NAME IS NOT NULL
BEGIN

    SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
    WHERE TABLE_NAME> @TABLE_NAME
    AND TABLE_NAME = 'sales'

    SELECT @AUDIT_TABLE = 'Audit'+''@TABLE_NAME''


    SELECT * INTO @AUDIT_TABLE
    FROM @TABLE_NAME

    TRUNCATE TABLE @AUDIT_TABLE
    ALTER TABLE @AUDIT_TABLE ADD UserAction Char(10),AuditStartTime  Char(50),AuditUser Char(50)


    SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
    WHERE TABLE_NAME> @TABLE_NAME
    AND TABLE_TYPE= 'BASE TABLE'
    AND TABLE_NAME!= 'sysdiagrams'
    AND TABLE_NAME!= 'Audit'
    AND TABLE_NAME NOT LIKE 'audit%'

END

Thanks. ..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-28 : 04:13:11

quote:

Originally posted by sauron

Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose.
For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work.
Please advise.. Thanks in advance.

Here is my code:


USE pubs

DECLARE @TABLE_NAME sysname
DECLARE @AUDIT_TABLE VARCHAR(50)
DECLARE @Sql varchar(8000)

SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME NOT LIKE 'audit%'
AND TABLE_NAME<> 'sysdiagrams'
AND TABLE_NAME<> 'Audit'
AND TABLE_NAME = 'sales'

WHILE @TABLE_NAME IS NOT NULL
BEGIN

    SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
    WHERE TABLE_NAME> @TABLE_NAME
    AND TABLE_NAME = 'sales'

    SELECT @AUDIT_TABLE = 'Audit'+ @TABLE_NAME


    SET @Sql='SELECT * INTO ' + @AUDIT_TABLE + 
    ' FROM ' + @TABLE_NAME + '

    TRUNCATE TABLE ' + @AUDIT_TABLE +'
    ALTER TABLE ' + @AUDIT_TABLE+ ' ADD UserAction Char(10),AuditStartTime  Char(50),AuditUser Char(50)'

     EXEC (@Sql)
    SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
    WHERE TABLE_NAME> @TABLE_NAME
    AND TABLE_TYPE= 'BASE TABLE'
    AND TABLE_NAME<> 'sysdiagrams'
    AND TABLE_NAME<> 'Audit'
    AND TABLE_NAME NOT LIKE 'audit%'

END

Thanks. ..

use <> fot not equal to in SQL Server rather than !=
Also build dynamic string from variables and use EXEC to execute

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-28 : 08:39:22

WHILE @TABLE_NAME IS NOT NULL
BEGIN

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

SELECT @AUDIT_TABLE = 'Audit'+@TABLE_NAME

exec ('SELECT *, UserAction Char(10),AuditStartTime Char(50),AuditUser Char(50) INTO ' + @AUDIT_TABLE + ' FROM ' + @TABLE_NAME)
END

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sauron
Starting Member

2 Posts

Posted - 2008-03-30 : 20:54:07

Thanks !! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources