loop to run 'Create Trigger' script?

If you are using 2000, use:

select name
from sysobjects
where xtype = 'tr'

for the select statement...

LouisDavidson at 2007-8-30 >

Just to clarify based on a comment from a related thread-

"On a related note, this is really not a very good approach. You should create a different trigger for every table (I like the approach of creating the triggers automatically using the loop, but not like this, as it will be problematic and slow)."

I am creating different triggers for each table, but they are created by a common script that dynamically determines the column names, etc. I would like a loop to run the create script for each table, as opposed to editing the table name and trigger name and running the script manually for each table.

steal at 2007-8-30 >

I really want to get it to work, but am short of time and just can't seem to get it right.

I would generate the table names from a populated cursor vs from sysobjects because not all of the tables in the db should be audited by this trigger, so I was thinking something like the following.

set nocount on
declare @cursor cursor
declare @tblTableNames table (TableName varchar(30))

insert into @tblTableNames (TableName) values ('tblCompanies')
insert into @tblTableNames (TableName) values ('tblDepartments')
insert into @tblTableNames (TableName) values ('tblManagementLevels').......

set @cursor = cursor for (select TableName
from @tblTableNames)
open @cursor

while (1=1)
begin
fetch next from @cursor into @tableName

if @@fetch_status <> 0
break

<create script code here.....>

end

close cursor
deallocate cursor

Also, something I am having trouble with is that my <create script code here.....> is full of somewhat complex code, embedded strings, etc. Posted below for your reading enjoyment...

if exists (select * from sysobjects where id =
object_id (N'dbo.tgDepartmentsAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgDepartmentsAudit
go

CREATE TRIGGER tgDepartmentsAudit on tblDepartments FOR insert, update, delete
AS
DECLARE @TableName varchar(128)
SET @TableName = 'tblDepartments'

-

DECLARE @fieldname varchar(128),
@pkJoinClause varchar(1000),
@sql nvarchar(2000),
@UpdateDate varchar(21),
@UserName varchar(128),
@TriggerType nchar(1),
@rowId int,
@maxRowId int,
@str1 varchar(100),
@str2 varchar(100)

SET @UserName = SYSTEM_USER
SET @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)

--detemine type of trigger
IF exists(select * FROM inserted) AND exists(select * from deleted)
SET @TriggerType = 'U'
else
IF exists(select * FROM inserted)
SET @TriggerType = 'I'
ELSE
SET @TriggerType = 'D'

--get all Column names for table
SELECT c1.COLUMN_NAME as colName, c1.ORDINAL_POSITION as RowId into #tblFieldNames
FROM INFORMATION_SCHEMA.TABLES t1
INNER JOIN INFORMATION_SCHEMA.COLUMNS c1 ON t1.TABLE_NAME = c1.TABLE_NAME
WHERE t1.TABLE_NAME = @tableName

-- Get PRIMARY KEY columns
select c.COLUMN_NAME as colName INTO #primaryKeyFields
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c on (c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)
WHERE pk.TABLE_NAME = @tableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'

--Create Join clause for primary key field(s)
SELECT @pkJoinClause = coalesce(@pkJoinClause + ' and', ' on') + ' i.' + PKF.colName + ' = d.' + PKF.colName
FROM #primaryKeyFields PKF

--Throw error if no primary key
IF @pkJoinClause IS NULL
BEGIN
raiserror('no PK ON TABLE %s', 16, -1, @TableName)
RETURN
END

-
-- the 'inserted' and 'deleted' tables have limitations, dump to temp tables for greater control
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

--get number of columns
select
@rowId = min(RowId),
@MaxRowId = max(RowId)
from #tblFieldNames

-- Loop through fields and build Sql string
while @RowId <= @MaxRowId
BEGIN
SELECT @fieldname = colName FROM #tblFieldNames WHERE RowId = @RowId

SELECT @sql = 'insert tblAuditAdmin (TableAltered, [Action], FieldName, OldValue, NewValue, UpdateDate, UpdateNumber, UserName)'
SELECT @sql = @sql + ' select ''' + @TableName + ''''
SELECT @sql = @sql + ',''' + @TriggerType + ''''
SELECT @sql = @sql + ',''' + @fieldname + ''''
SELECT @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
SELECT @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ', 1'
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @sql = @sql + @pkJoinClause
SELECT @sql = @sql + ' WHERE (''' + @TriggerType + ''' = ''I'')'
SELECT @sql = @sql + ' OR (''' + @TriggerType + ''' = ''D'')'
SELECT @sql = @sql + ' OR (''' + @TriggerType + ''' = ''U'' AND '
SELECT @sql = @sql + '((i.' + @fieldname + ' <> d.' + @fieldname + ')'
SELECT @sql = @sql + ' OR (''' + @fieldname + ''' in (Select colName from #primaryKeyFields))'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NULL AND d.' + @fieldname + ' is NOT null)'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NOT NULL AND d.' + @fieldname + ' is null)))'

EXEC (@sql)
set @RowId = @RowId + 1
END

Drop Table #ins
Drop Table #del
Drop Table #tblFieldNames
Drop Table #primaryKeyFields

go

Incidentally, if you're interested in what the actual audit table looks like, run this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblAuditAdmin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblAuditAdmin]
GO

CREATE TABLE [dbo].[tblAuditAdmin] (
[UpdateDate] [datetime] NOT NULL ,
[TableAltered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateNumber] [int] NULL ,
[Action] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OldValue] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewValue] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblAuditAdmin] ADD
CONSTRAINT [PK_tblAuditAdmin] PRIMARY KEY CLUSTERED
(
[UpdateDate],
[TableAltered],
[FieldName]
) ON [PRIMARY]
GO

steal at 2007-8-30 >

Oh, I get it! This worked great, thanks to Aego!

For what it's worth, the following is what I ended up with.

-- To create this trigger for each table, insert the table name into the 2nd line of code (set @TableName = 'TblCompanies'),
-- then highlight and execute the "first block of code" (inside dashed lines). Then paste the output from this into the
-- second block. Select that code all the way to the bottom and execute to create the trigger for that table. Do this
-- for each table name that you want the trigger created for. Alternatively, you can just set the table name and trigger
-- name manually for each table in the second block of code.

--First block of code...
declare @TableName varchar(50)
set @TableName = 'tblDepartments'

declare @string varchar(5000)
set @string = 'if exists (select * from sysobjects where id = object_id (N''dbo.tg' + @TableName + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)' + char(13) +
'drop trigger dbo.tg' + @TableName + 'Audit ' + + char(13) +
'go ' + char(13) +

'CREATE TRIGGER tg' + @TableName + 'Audit on ' + @TableName + ' FOR insert, update, delete
AS
DECLARE @TableName varchar(128)
SET @TableName = ''' + @TableName + '''
' + char(13)

print @string

--Second block of code...
--replace code in this block with the output from above block and execute from here down
if exists (select * from sysobjects where id = object_id (N'dbo.tgtblDepartmentsAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgtblDepartmentsAudit
go
CREATE TRIGGER tgtblDepartmentsAudit on tblDepartments FOR insert, update, delete
AS
DECLARE @TableName varchar(128)
SET @TableName = 'tblDepartments'

...... the rest of my code.......

steal at 2007-8-30 >