Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Deleting logon trigger. Expand / Collapse
Author
Message
Posted Monday, December 5, 2011 9:52 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
GilaMonster (12/5/2011)
zorge (12/5/2011)
Yet, for some reason the LOGON trigger on ALL SERVER is not listed in sys.triggers.


No mysterious reason. Sys.triggers is for database-scoped triggers. You'll find server-scoped triggers in sys.server_triggers



Please note, amost 1 YO thread






Yes, I know, good point to post!
Post #1216454
Posted Monday, December 5, 2011 12:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 11:17 AM
Points: 3, Visits: 21
I don't see the benefit of creating a blank trigger. If trigger already exists creation of blank trigger would fail. If trigger does not exist drop trigger would fail.

I was considering creating a new thread, but thought that the issues are closely related,.
Post #1216617
Posted Monday, December 5, 2011 12:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 11:17 AM
Points: 3, Visits: 21
Thanks. That is exactly what I was looking for.

The MSDN article I was referring to (http://msdn.microsoft.com/en-us/library/ms173497.aspx) states that:

Because DDL triggers are not schema-scoped and, therefore do not appear in the sys.objects catalog view, the OBJECT_ID function cannot be used to query whether they exist in the database. Objects that are not schema-scoped must be queried by using the appropriate catalog view. For DDL triggers, use sys.triggers.

That was the reason for my confusion. Thanks again.

Post #1216620
Posted Monday, December 5, 2011 2:02 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 40,609, Visits: 37,068
ddl triggers are in sys.triggers as they are database-scoped triggers. Server triggers however are not. Which database's sys.triggers would they go into?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1216679
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse