[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Create triggers for all tables using a loop

Posted on 2008-01-30
14
Medium Priority
?
732 Views
Last Modified: 2008-01-30
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
0
Comment
Question by:fuerteventura
  • 7
  • 7
14 Comments
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20775915
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
 

Author Comment

by:fuerteventura
ID: 20775974
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
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20776010
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:fuerteventura
ID: 20776148
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
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20776193
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
 

Author Comment

by:fuerteventura
ID: 20776329
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
 

Author Comment

by:fuerteventura
ID: 20776407
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
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20776536
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
 

Author Comment

by:fuerteventura
ID: 20776670
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
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20776691
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
 

Author Comment

by:fuerteventura
ID: 20776721
OK Thanks!
0
 
LVL 23

Accepted Solution

by:
Ashish Patel earned 2000 total points
ID: 20777057
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
 

Author Comment

by:fuerteventura
ID: 20777266
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
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20777281
You will definately be much better than me. Welcome again!!!
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question