SQL Tables not getting picked up.

[murphyhg]

I am using a script to create audit trigger tables. however these triggers keep getting created and I do not want them created. The tables are not but the triggers are.(Layout_ChangeTracking, Notes_ChangeTracking, siteimages_ChangeTracking)

SELECT @TABLE_NAME = MIN(TABLE_NAME)

FROM

INFORMATION_SCHEMA.TABLES

WHERE

TABLE_TYPE= 'BASE TABLE'

AND (TABLE_NAME != 'sysdiagrams'

AND RIGHT(TABLE_NAME, 6) != '_AUDIT' AND TABLE_NAME != 'article_status' AND TABLE_NAME != 'Layout' AND TABLE_NAME != 'Notes' AND TABLE_NAME != 'siteimages')

--Loop through the tables

WHILE @TABLE_NAME IS NOT NULL

BEGIN

-- Initialize/reset variables

SET @Create_Audit_Table_SQL = 'CREATE TABLE ' + @TABLE_NAME + '_AUDIT ('

SET @Create_Audit_Col_Old = ''

SET @Trigger_Select_Col_Old = ''

SET @Trigger_Select_Col_Current = ''

 

[murphyhg]

I solved this issue. Here is the code I used to do it.
DECLARE

@Create_Audit_Table_SQL varchar(MAX),

@Create_Trigger_SQL varchar(MAX),

@TABLE_NAME sysname,

@Trigger_Select_Col_Current varchar(MAX),

@Create_Audit_Col_Old varchar(MAX),

@Trigger_Select_Col_Old varchar(MAX),

@COLUMN VARCHAR(MAX),

@COLUMN_NAME VARCHAR(50)

SET NOCOUNT ON

--Select all the tables in the database but the Audit tables and the sysdiagram table

SELECT @TABLE_NAME = MIN(TABLE_NAME)

FROM

INFORMATION_SCHEMA.TABLES

WHERE

TABLE_TYPE= 'BASE TABLE'

AND (TABLE_NAME != 'sysdiagrams'

AND RIGHT(TABLE_NAME, 6) != '_AUDIT' AND TABLE_NAME != 'article_status' AND TABLE_NAME != 'Layout' AND TABLE_NAME != 'Notes' AND TABLE_NAME != 'siteimages')

--Loop through the tables

WHILE @TABLE_NAME IS NOT NULL

BEGIN

-- Initialize/reset variables

SET @Create_Audit_Table_SQL = 'CREATE TABLE ' + @TABLE_NAME + '_AUDIT ('

SET @Create_Audit_Col_Old = ''

SET @Trigger_Select_Col_Old = ''

SET @Trigger_Select_Col_Current = ''

-- Create a cursor for looping through the columns in the table

DECLARE ColumnInfo_cursor CURSOR FOR

SELECT

CASE

-- Creating the formatted column line for a create table column

WHEN DATA_TYPE IN ('decimal', 'numeric') THEN CONVERT(VARCHAR(MAX), COLUMN_NAME + ' ' + DATA_TYPE + '(' + CONVERT(VARCHAR(10), NUMERIC_PRECISION) + ',' + CONVERT(VARCHAR(10), NUMERIC_SCALE)+ '), ')

WHEN CHARACTER_MAXIMUM_LENGTH IS NULL OR DATA_TYPE = 'image' OR DATA_TYPE IN ('ntext', 'image', 'text', 'xml', 'sql_variant') THEN CONVERT(VARCHAR(MAX), COLUMN_NAME + ' ' + DATA_TYPE + ', ')

WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN CONVERT(VARCHAR(MAX), COLUMN_NAME + ' ' + DATA_TYPE + '(MAX), ')

ELSE CONVERT(VARCHAR(MAX), COLUMN_NAME + ' ' + DATA_TYPE + '(' + CONVERT(VARCHAR(10), CHARACTER_MAXIMUM_LENGTH) + '), ')

END

, COLUMN_NAME

FROM

INFORMATION_SCHEMA.COLUMNS

Where

TABLE_NAME = @TABLE_NAME

ORDER BY ORDINAL_POSITION

OPEN ColumnInfo_cursor

FETCH NEXT FROM ColumnInfo_cursor

INTO @COLUMN, @COLUMN_NAME

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Create_Audit_Table_SQL = @Create_Audit_Table_SQL + @COLUMN

SET @Create_Audit_Col_Old = @Create_Audit_Col_Old + 'old' + @COLUMN

SET @Trigger_Select_Col_Current = @Trigger_Select_Col_Current + @COLUMN_NAME + ', '

SET @Trigger_Select_Col_Old = @Trigger_Select_Col_Old + 'old' + @COLUMN_NAME + ', '

FETCH NEXT FROM ColumnInfo_cursor

INTO @COLUMN, @COLUMN_NAME

END

--Add to more rows to the table for a date and type stamp

SET @Create_Audit_Table_SQL = @Create_Audit_Table_SQL + @Create_Audit_Col_Old + 'AuditDate datetime DEFAULT (GetDate()), AuditType char(1))'

CLOSE ColumnInfo_cursor

DEALLOCATE ColumnInfo_cursor

-- Uncomment out the code line below if you want to drop your audit tables

-- WARNING: You will lose all history of the audit table if you do this

-- EXEC('IF OBJECT_ID (''' + @TABLE_NAME + '_AUDIT'') IS NOT NULL DROP TABLE ' + @TABLE_NAME + '_AUDIT')

-- Create the audit table if it doesn't exist. Do not create audit tables for Audit tables

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= @TABLE_NAME + '_AUDIT') AND RIGHT(@TABLE_NAME, 6) != '_AUDIT' AND @TABLE_NAME != 'article_status' AND @TABLE_NAME != 'Layout' AND @TABLE_NAME != 'Notes' AND @TABLE_NAME != 'siteimages'

BEGIN

-- PRINT @Create_Audit_Table_SQL

SELECT @Create_Audit_Table_SQL

EXEC(@Create_Audit_Table_SQL)

END

-- Create triggers for all tables except the audit tables

IF RIGHT(@TABLE_NAME, 6) != '_AUDIT' AND @TABLE_NAME != 'article_status' AND @TABLE_NAME != 'Layout' AND @TABLE_NAME != 'Notes' AND @TABLE_NAME != 'siteimages'

BEGIN

-- Delete the trigger if it exist

EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')

-- Create the trigger

SET @Create_Trigger_SQL = 'create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete

as

declare @AuditType char(1), @PKCols VARCHAR(MAX), @SQL VARCHAR(MAX)

--Find the Primary keys to be used in the inserted and deleted outer join

select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = ''' + @TABLE_NAME + '''

and CONSTRAINT_TYPE = ''PRIMARY KEY''

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

select * into #ins from inserted

select * into #del from deleted

if exists (select * from inserted)

if exists (select * from deleted)

SET @AuditType = ''U''

else

SET @AuditType = ''I''

else

SET @AuditType = ''D''

EXEC(''INSERT INTO ' + @TABLE_NAME + '_AUDIT ('

-- Add the columns - current and old to the select

SET @Create_Trigger_SQL = @Create_Trigger_SQL + @Trigger_Select_Col_Current + @Trigger_Select_Col_Old + ' AuditType)

SELECT i.*, d.*, '''''' + @AuditType + '''''' FROM #ins i full outer join #del d '' + @PKCols )'

-- PRINT @Create_Trigger_SQL

SELECT @Create_Trigger_SQL

EXEC(@Create_Trigger_SQL)

END

-- Next table

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

WHERE TABLE_NAME > @TABLE_NAME

AND TABLE_TYPE= 'BASE TABLE'

AND TABLE_NAME!= 'sysdiagrams'

END