We help IT Professionals succeed at work.

Script to disable triggers where the name begins with a certain prefix in SQL2008R2x64

rdraut
rdraut used Ask the Experts™
on
Good morning...

I would like a script to disable triggers where the name begins with a certain prefix in SQL2008R2x64 so I dont have to do it manually.

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Anuradha GoliSystems Development / Support Specialist

Commented:
To Manually disable individual trigger
Disable Trigger Triggername on databasename;

Open in new window


But to disable all triggers in the database.
SELECT 
'Disable Trigger ' + name + ' On '
+ OBJECT_NAME(parent_id)  
FROM sys.triggers 
WHERE 
name like 'SQL2008R2x64%'

Open in new window


The number of rows in the result is the script to disable all the triggers.
Execute the Results all once to disable triggers in the database.
I always create a second script to enable them again at the same time.  the reason I do this as there might have been another trigger already disabled.  If it was disabled for a different reason, you probably do not want to enable it accidently when you bring these back up.

And if you just create a re-enable all disabled scripts at teh time you are ready to bring these back up that is what will happen.  So my disable and re-enable scriptsare usually generated in pairs...
nishant joshiTechnology Development Consultant

Commented:
chnaing code of anuradhey

Declare @sql varchar(max) = ''
SELECT @sql = @sql +'Disable Trigger ' + name + ' On '
+ OBJECT_NAME(parent_id)  
FROM sys.triggers 
WHERE 
name like 'SQL2008R2x64%'
exec(@sql)

Open in new window


check above code.

Thanks and Regards,
nishant

Author

Commented:
I should clarify, the triggers I want to disable all begin with 'Z_' if that helps.
I made a few minor changes as our system uses schemas and the sample above did not allow for that.  I also included a select before the execute so that you can see the code that was created to make it easier to debug if you get an error. (I always recommend reviewing the code manually before trusting it, anyway...)   In addition, the original script had problems when the parent id was 0, so I added a test for parent id = 0 (basically a system trigger) and ignored them as I hope you are not trying to disable system triggers.

Lastly, our system triggers begin with 'Trg'. So just replace 'Trg' with 'Z_' and I believe this will work for you.


Declare @sql varchar(max)
set @sql = ''
SELECT @sql = @sql +' Enable Trigger ' +   t.name + ' On '
+ s.name + '.' + o.name  + ' ; '
FROM sys.triggers t inner join
     sys.objects o   on t.parent_id = o.object_id
     inner join
     sys.schemas s   on s.schema_id = o.schema_id
WHERE  parent_id <> 0
and name like 'Trg%'
select @sql
exec(@sql)

Sorry for the delay.  you probably already figured this out, but I thought the follow_up might help others with similiar issues.