SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 DDL Trigger listing on SQL server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gangadhara.ms
Aged Yak Warrior

India
547 Posts

Posted - 02/07/2010 :  05:33:17  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/07/2010 :  10:52:48  Show Profile  Reply with Quote
SELECT * FROM sys.triggers WHERE parent_class=0


will list out all the DDL triggers in your db
Go to Top of Page

gangadhara.ms
Aged Yak Warrior

India
547 Posts

Posted - 02/08/2010 :  05:08:42  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/08/2010 :  05:27:29  Show Profile  Reply with Quote
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.
Go to Top of Page

gangadhara.ms
Aged Yak Warrior

India
547 Posts

Posted - 02/08/2010 :  07:52:47  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/08/2010 :  08:06:03  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/08/2010 :  08:23:43  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/08/2010 :  08:58:35  Show Profile  Reply with Quote
Ah, well it was a wild guess, but it looks the same as:

http://msdn.microsoft.com/en-us/library/ms173497.aspx (Drop Trigger)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/08/2010 :  09:06:48  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000