loop to run 'Create Trigger' script?

I need to run a script to create a trigger on 18 tables on 3 databases. The code is identical with the exception of the table and trigger names at the beginning. Does anyone know of a way to create them all with a loop instead of manually replacing the table and trigger names and executing over and over? I tried variables but get an 'Incorrect syntax near '@.TriggerName' error.

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

CREATE TRIGGER tgUsersAudit on tblUsers FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = tblUsers

..................from here the code is the same for all

You can add to this script.

set nocount on
declare @.cursor cursor,
@.triggerName sysname

set @.cursor = cursor for (select name
from sys.triggers)
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.triggerName
if @.@.fetch_status <> 0
break

select @.triggername

end

If you are using 2000, use:

select name
from sysobjects
where xtype = 'tr'

for the select statement...

|||

The triggers don't yet exist, so they won't be in sysobjects.

I tried putting the 'create trigger' code in a sp, with table name and trigger name as parameters but get a syntax error when using a variable.

|||

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.

|||

Sorry, I misunderstood :) Will this work for you:

set nocount on
declare @.cursor cursor,
@.tableName sysname

set @.cursor = cursor for (select name
from sys.tables)
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.tableName
if @.@.fetch_status <> 0
break

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

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

<more code here>
'

end

|||Yes, this is along the lines of what I want to do- however... my code is just too ugly and I'm struggling with getting the string all to concatenate correctly (can't get the quotes right and don't have time to play with it right now). I guess for now I'll just have to run it separately for each table- boo hoo!|||

Though you have said you want to do it one table by one table, I am still put this code here incase it can save you some energy.

select 'if exists (select * from sysobjects where id = object_id (N''dbo.tg' + name + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)' + char(13) +
'drop trigger dbo.tg' + name + 'Audit
go'
from sys.tables

SELECT '
CREATE TRIGGER tg' + name + 'Audit on ' + name + ' FOR insert, update, delete
AS
DECLARE name varchar(128)
SET name = ''' + name + '''
' + char(13) +
'<more code here>
' + char(13) +
char(13)
from sys.tables

This will generate two batch. First one clean existing trigger. The scaond one has the head part of each trigger. Replace "<more code here>" with your common code. That will give you the excutable batch to do the job.

|||

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

|||

Sorry. Forgot to tell you how to use the code I posted yesterday.

Change the output of Query Analyzer to 'text'. excute the code. Copy the output to edit portion of Query Analyzer. Then replace "<more code here>" with the common part of your code. Now you will have the script.

This makeshift works well, If you don't want to spend too much time.

|||

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.......