DDL Trigger listing on SQL server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

DDL Trigger listing on SQL server

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-02-07 : 05:33:17

Dear All,

I have created the trigger on database for alter table to rollback all the alter table commands.

But now i need to disable to make changes now unfortunately i have forgotten the Trigger name itself(i am sorry for this).

How to know which are all DDL triggers are there in particular server.??

Thanks in advance
Gangadhar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-07 : 10:52:48

[code]SELECT * FROM sys.triggers WHERE parent_class=0[/code]

will list out all the DDL triggers in your db

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-02-08 : 05:08:42

Hi I used this query..
select * from sysobjects where xtype='TR' but my DDL trigger is not listing in this.
I will try the above query and let you if it works
Thanks,
Gangadhar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 05:27:29

dont use system tables sysobjects from 2005 onwards. they're provided only for backward compatibility. use catalog views like sys.objects instead

In your case you cant use sys.objects too.the below excerpt is from BOL

sys.objects does not show DDL triggers, because they are not schema-scoped. All triggers, both DML and DDL, are found in sys.triggers. sys.triggers supports a mixture of name-scoping rules for the various kinds of triggers.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-02-08 : 07:52:47

Hi Visakh,
Thanks a lot for your reply.

Also i need one more help even i have tried to search it in BOL but i am not able to get the same excerpt.

Can you please teach me how to get max usage from BOL ? I am new to SQL not sure how to use BOL for what we need exactly

Thanks in Advance,
Gangadhar

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 08:06:03

BOL is the documentation for SQL Server - use it like any other technical reference manual

Visakh's reference is under DROP TRIGGER (and probably other places too)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 08:23:43

quote:
Originally posted by Kristen

BOL is the documentation for SQL Server - use it like any other technical reference manual

Visakh's reference is under DROP TRIGGER (and probably other places too)

Sorry what I've quoted is from sys.objects explanation

see
http://msdn.microsoft.com/en-us/library/ms190324.aspx

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 08:58:35

Ah, well it was a wild guess, but it looks the same as:

http://msdn.microsoft.com/en-us/library/ms173497.aspx (Drop Trigger)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 09:06:48

quote:
Originally posted by Kristen

Ah, well it was a wild guess, but it looks the same as:

http://msdn.microsoft.com/en-us/library/ms173497.aspx (Drop Trigger)

yeah..it is

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources