script all trigger in a data base

Question

Post reply

script all trigger in a data base

elham_azizi_62

SSCertifiable

Points: 5095
More actions
September 22, 2010 at 1:09 am

#224284

hi all.
how to create script all trigger that exist in data base already?but not by 'generate script' in it's sql server.
by store procedure.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

have a look of this post :-

http://it.toolbox.com/wiki/index.php/Find_all_the_triggers_in_a_database..

if you not able to open it then 2 query mentioned there as:-

SELECT S2.[name] TableName, S1.[name] TriggerName, CASE WHEN S1.deltrig > 0

THEN 'Delete' WHEN S1.instrig > 0 THEN 'Insert' WHEN S1.updtrig > 0 THEN 'Update'

END 'TriggerType' FROM sysobjects S1 JOIN sysobjects S2 ON S1.parent_obj = S2.[id] WHERE S1.xtype='TR'

SELECT S2.[name] TableName, S1.[name] TriggerName, CASE WHEN S2.deltrig = s1.id THEN 'Delete'

WHEN S2.instrig = s1.id THEN 'Insert' WHEN S2.updtrig = s1.id THEN 'Update' END 'TriggerType' ,

'S1',s1.*,'S2',s2.* FROM sysobjects S1 JOIN sysobjects S2 ON S1.parent_obj = S2.[id] WHERE S1.xtype='TR'

----------
Ashish

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

This is a bit simpler and uses the newer objects in the SYS schema:

SELECT

SM.definition

FROM

sys.triggers AS T JOIN

sys.sql_modules AS SM

ON T.object_id = SM.object_id

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

crazy4sql SSCoach Points: 19590 More actions · Answer 3

really simple and best.

thanks for sharing. updated my sql library

----------
Ashish

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 4

I have a schedule which scripts all objects in all db's using powershell. I'm sure you can find many articles online but this is one I quickly found that scripts tables.

Simple Talk Article[/url]