Create triggers for all tables using a loop

I have created an audit trigger in SQL Server 2005 (although i also need it to work in SQL Server 2000)which is working fine. I need it to work on all tables in the database. I can run the routine to create it for all tables one by one but the problem is if I need to ammend it, I have to go back and change it for all of the tables! Is there a way I can generically create a trigger for all tables? One idea I have could work as follows - please note my Transact SQL is not up to speed (or lousy!) so I would require help in inserting the correct syntax!::

Select tableName from SomeTableHoldingAllTheTablesInMyDatabase
do until NoMoreRecords
       @tablename=tablename  
        if triggerAudit on @tableName exists then
               drop triggeraudit
        create trigger on @tablename
           (Code to create the trigger)
Loop

Is this possible to do? Many thanks for looking
fuerteventuraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ashish PatelCommented:
use the below select

select 'Create trigger on ' + object_name(id) +  '(Code to create the trigger)' from sysobjects where xtype = 'U'

and you will get rows of creating triggers statement as output, just copy them or loop them as cursor and execute them.
0
fuerteventuraAuthor Commented:
Many thanks for that. Seems like an excellent solution - I'm very new to SQL Server so 2 further questions on this:
1 - How do you loop them as a cursor?
2 - Will this work as a stored procedure that I can just ammend and call each time i want to make changes to the trigger(s)?
0
Ashish PatelCommented:
This is the cursor code just put this in stored procedure and all is done. But please change the Code to create the trigger part :)

Declare @Sql varchar(8000)
 
IF Cursor_Status('variable', 'mycursor') >= 0
	Begin
		Close mycursor
		Deallocate mycursor		
	End
 
Declare mycursor Cursor For select 'Create trigger on ' + object_name(id) +  '(Code to create the trigger)' from sysobjects where xtype = 'U'
 
Open mycursor 
Fetch Next From mycursor Into @Sql
While @@Fetch_Status=0
Begin
	Exec (@Sql)
	Fetch Next From mycursor Into @Sql
End		
Close 	mycursor
Deallocate mycursor

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

fuerteventuraAuthor Commented:
Many thanks again - I've tried your code (both with my create trigger statement and also with just a 'select object_name(id) from sysobjects where xtype = "U"')

I'm getting the following message when running with my create trigger statement declared:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.

And the following message when running with the select object_name(id) from sysobjects where xtype = "U":

Msg 207, Level 16, State 1, Line 1
Invalid column name 'U'.

I guess it's something to do with putting quations within quotations - what's the way around this? I tried substituing the ' for " but to no avail! Cheers.
0
Ashish PatelCommented:
actually if you see the statement what we made should have the trigger name before the ON like this. So please use the cursor statement like i have used. Also as i said please change the "Code to create the trigger" part in the sleect statement and place your code of trigger in it. Or let me know one actual trigger sample which you want to apply on all tables.

Declare mycursor Cursor For select 'Create trigger Trigger_' + object_name(id) + ' on ' + object_name(id) +  '(Code to create the trigger)' from sysobjects where xtype = 'U'
 
0
fuerteventuraAuthor Commented:
Still can't get it to work - it says that the Command(s) have been executed successfully but it's not creating any triggers now - here is the syntax which successfully created my trigger - where i declare
@TableName, I will obviously have to replace myTable with ' + object_name(id) + '

Create trigger [tr_trigtest3] on [dbo].[Mytable] for insert, update, delete
as

declare @bit int ,
      @field int ,
      @maxfield int ,
      @char int ,
      @fieldname varchar(128) ,
      @TableName varchar(128) ,
      @PKCols varchar(1000) ,
      @sql varchar(2000),
      @UpdateDate varchar(21) ,
      @UserName varchar(128) ,
      @Type char(1) ,
      @PKSelect varchar(1000)
     
      select @TableName = 'MyTable'

      -- date and user
      select       @UserName = system_user ,
            @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)

      -- Action
      if exists (select * from inserted)
            if exists (select * from deleted)
                  select @Type = 'U'
            else
                  select @Type = 'I'
      else
            select @Type = 'D'
     
      -- get list of columns
           select *  into #ins from inserted
             select *  into #del from deleted
     
      -- Get primary key columns for full 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 = @TableName
      and      CONSTRAINT_TYPE = 'PRIMARY KEY'
      and      c.TABLE_NAME = pk.TABLE_NAME
      and      c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
     
      -- Get primary key select for insert
      select @PKSelect = coalesce(@PKSelect+'+','') + '''<' + COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
      from      INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
      where       pk.TABLE_NAME = @TableName
      and      CONSTRAINT_TYPE = 'PRIMARY KEY'
      and      c.TABLE_NAME = pk.TABLE_NAME
      and      c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
     
      if @PKCols is null
      begin
            raiserror('no PK on table %s', 16, -1, @TableName)
            return
      end
     
      select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
      while @field < @maxfield
      begin
            select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
            select @bit = (@field - 1 )% 8 + 1
            select @bit = power(2,@bit - 1)
            select @char = ((@field - 1) / 8) + 1
            if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
            begin
                  select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
                  select @sql =             'insert Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'
                  select @sql = @sql +       ' select ''' + @Type + ''''
                  select @sql = @sql +       ',''' + @TableName + ''''
                  select @sql = @sql +       ',' + @PKSelect
                  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 +       ',''' + @UserName + ''''
                  select @sql = @sql +       ' from #ins i full outer join #del d'
                  select @sql = @sql +       @PKCols
                  select @sql = @sql +       ' where i.' + @fieldname + ' <> d.' + @fieldname
                  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)
            end
      end
0
fuerteventuraAuthor Commented:
UPDATE - I think the only problem I've got now is how to use the ' character in the string where I create the Trigger. I tried replaciong all the ' with " but got loads of errors stating

Msg 102, Level 15, State 1, Procedure Trigger_tblLeftTemp, Line 17
Incorrect syntax near ''.
Msg 102, Level 15, State 1, Procedure Trigger_tblLeftTemp, Line 21
Incorrect syntax near ''.

How do I effectively use the ' and have it create the trigger as intended?
0
Ashish PatelCommented:
Yes i guess the problem is with enter key character too, so place your code first in notepad and then try creating the trigger. The string is too long, but i think we would be able to do that.
0
fuerteventuraAuthor Commented:
I didn't notice that it has put near ''.
In SQL Server it said near '"'. This happened when I replaced all the ' with ". It's not the enter key it's the quoation marks
0
Ashish PatelCommented:
Let me get the script ready for you, and then you just will have to put that script in the stored procedure. Give me couple of mins.
0
fuerteventuraAuthor Commented:
OK Thanks!
0
Ashish PatelCommented:
Please note the triggers would be created by names like Trigger_TableName1, Trigger_TableName2 ....

Use the below script, you will get idea about it.

 
Declare @Sql varchar(8000)
 
IF Cursor_Status('variable', 'mycursor') >= 0
	Begin
		Close mycursor
		Deallocate mycursor		
	End
 
Declare mycursor Cursor For select object_name(id) from sysobjects where xtype = 'U'
 
Open mycursor 
Fetch Next From mycursor Into @Sql
While @@Fetch_Status=0
Begin
 
decalre @sqlstr as varchar(8000)
 
set @sqlstr = 'Create trigger Trigger_' + @Sql + ' on dbo.' + @Sql + ' for insert, update, delete as '
	set @sqlstr = @sqlstr + 'declare @bit int ,' 
	set @sqlstr = @sqlstr + '      @field int ,' 
	set @sqlstr = @sqlstr + '      @maxfield int ,' 
	set @sqlstr = @sqlstr + '      @char int ,' 
	set @sqlstr = @sqlstr + '      @fieldname varchar(128) ,' 
	set @sqlstr = @sqlstr + '      @TableName varchar(128) ,' 
	set @sqlstr = @sqlstr + '      @PKCols varchar(1000) ,' 
	set @sqlstr = @sqlstr + '      @sql varchar(2000), ' 
	set @sqlstr = @sqlstr + '      @UpdateDate varchar(21) ,' 
	set @sqlstr = @sqlstr + '      @UserName varchar(128) ,' 
	set @sqlstr = @sqlstr + '      @Type char(1) ,' 
	set @sqlstr = @sqlstr + '      @PKSelect varchar(1000)' 
	set @sqlstr = @sqlstr + '      ' 
	set @sqlstr = @sqlstr + '      select @TableName = ''' + @sql + '''' 
	set @sqlstr = @sqlstr + '' 
	set @sqlstr = @sqlstr + '      -- date and user' 
	set @sqlstr = @sqlstr + '      select       @UserName = system_user ,' 
	set @sqlstr = @sqlstr + '            @UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)' 
	set @sqlstr = @sqlstr + '' 
	set @sqlstr = @sqlstr + '      -- Action' 
	set @sqlstr = @sqlstr + '      if exists (select * from inserted)' 
	set @sqlstr = @sqlstr + '            if exists (select * from deleted)' 
	set @sqlstr = @sqlstr + '                  select @Type = ''U''' 
	set @sqlstr = @sqlstr + '            else' 
	set @sqlstr = @sqlstr + '                  select @Type = ''I''' 
	set @sqlstr = @sqlstr + '      else' 
	set @sqlstr = @sqlstr + '            select @Type = ''D''' 
	set @sqlstr = @sqlstr + '      ' 
	set @sqlstr = @sqlstr + '      -- get list of columns' 
	set @sqlstr = @sqlstr + '           select *  into #ins from inserted' 
	set @sqlstr = @sqlstr + '             select *  into #del from deleted' 
	set @sqlstr = @sqlstr + '      ' 
	set @sqlstr = @sqlstr + '      -- Get primary key columns for full outer join' 
	set @sqlstr = @sqlstr + '      select      @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME' 
	set @sqlstr = @sqlstr + '      from      INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,' 
	set @sqlstr = @sqlstr + '            INFORMATION_SCHEMA.KEY_COLUMN_USAGE c' 
	set @sqlstr = @sqlstr + '      where       pk.TABLE_NAME = @TableName' 
	set @sqlstr = @sqlstr + '      and      CONSTRAINT_TYPE = ''PRIMARY KEY''' 
	set @sqlstr = @sqlstr + '      and      c.TABLE_NAME = pk.TABLE_NAME' 
	set @sqlstr = @sqlstr + '      and      c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME' 
	set @sqlstr = @sqlstr + '      ' 
	set @sqlstr = @sqlstr + '      -- Get primary key select for insert' 
	set @sqlstr = @sqlstr + '      select @PKSelect = coalesce(@PKSelect+''+'','''') + ''''''<'' + COLUMN_NAME + ''=''''+convert(varchar(100),coalesce(i.'' + COLUMN_NAME +'',d.'' + COLUMN_NAME + ''))+''''>'''''' ' 
	set @sqlstr = @sqlstr + '      from      INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,' 
	set @sqlstr = @sqlstr + '            INFORMATION_SCHEMA.KEY_COLUMN_USAGE c' 
	set @sqlstr = @sqlstr + '      where       pk.TABLE_NAME = @TableName' 
	set @sqlstr = @sqlstr + '      and      CONSTRAINT_TYPE = ''PRIMARY KEY''' 
	set @sqlstr = @sqlstr + '      and      c.TABLE_NAME = pk.TABLE_NAME' 
	set @sqlstr = @sqlstr + '      and      c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME' 
	set @sqlstr = @sqlstr + '      ' 
	set @sqlstr = @sqlstr + '      if @PKCols is null' 
	set @sqlstr = @sqlstr + '      begin' 
	set @sqlstr = @sqlstr + '            raiserror(''no PK on table %s'', 16, -1, @TableName)' 
	set @sqlstr = @sqlstr + '            return' 
	set @sqlstr = @sqlstr + '      end' 
	set @sqlstr = @sqlstr + '      ' 
	set @sqlstr = @sqlstr + '      select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName' 
	set @sqlstr = @sqlstr + '      while @field < @maxfield' 
	set @sqlstr = @sqlstr + '      begin' 
	set @sqlstr = @sqlstr + '            select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field' 
	set @sqlstr = @sqlstr + '            select @bit = (@field - 1 )% 8 + 1' 
	set @sqlstr = @sqlstr + '            select @bit = power(2,@bit - 1)' 
	set @sqlstr = @sqlstr + '            select @char = ((@field - 1) / 8) + 1' 
	set @sqlstr = @sqlstr + '            if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')' 
	set @sqlstr = @sqlstr + '            begin' 
	set @sqlstr = @sqlstr + '                  select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field' 
	set @sqlstr = @sqlstr + '                  select @sql =             ''insert Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)''' 
	set @sqlstr = @sqlstr + '                  select @sql = @sql +       '' select '''''' + @Type + ''''''''' 
	set @sqlstr = @sqlstr + '                  select @sql = @sql +       '','''''' + @TableName + ''''''''' 
	set @sqlstr = @sqlstr + '                  select @sql = @sql +       '','' + @PKSelect' 
	set @sqlstr = @sqlstr + '                  select @sql = @sql +       '','''''' + @fieldname + ''''''''' 
	set @sqlstr = @sqlstr + '                  select @sql = @sql +       '',convert(varchar(1000),d.'' + @fieldname + '')''' 
	set @sqlstr = @sqlstr + '                  select @sql = @sql +       '',convert(varchar(1000),i.'' + @fieldname + '')''' 
	set @sqlstr = @sqlstr + '                  select @sql = @sql +       '','''''' + @UpdateDate + ''''''''' 
	set @sqlstr = @sqlstr + '                  select @sql = @sql +       '','''''' + @UserName + ''''''''' 
	set @sqlstr = @sqlstr + '                  select @sql = @sql +       '' from #ins i full outer join #del d''' 
	set @sqlstr = @sqlstr + '                  select @sql = @sql +       @PKCols' 
	set @sqlstr = @sqlstr + '                  select @sql = @sql +       '' where i.'' + @fieldname + '' <> d.'' + @fieldname ' 
	set @sqlstr = @sqlstr + '                  select @sql = @sql +       '' or (i.'' + @fieldname + '' is null and  d.'' + @fieldname + '' is not null)'' ' 
	set @sqlstr = @sqlstr + '                  select @sql = @sql +       '' or (i.'' + @fieldname + '' is not null and  d.'' + @fieldname + '' is null)'' ' 
	set @sqlstr = @sqlstr + '                  exec (@sql)' 
	set @sqlstr = @sqlstr + '            end' 
	set @sqlstr = @sqlstr + '      end' 
 
 
	Exec (@sqlstr)
	Fetch Next From mycursor Into @Sql
End		
Close 	mycursor
Deallocate mycursor

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fuerteventuraAuthor Commented:
Thank you so much for all your help. I hope one day I will be at your level of expertise with SQL Server! Cheers.
0
Ashish PatelCommented:
You will definately be much better than me. Welcome again!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.