Where are my Triggers?
-
I'm seriously confused.
from the screenshot below, my local dev server has two server level DDL triggers.
From the object explorer, i can right click and script them out, no problem.
but if i try to select * from sys.triggers or sp_helptext [Create_Database_Trigger]
i get no results or an error like this:
Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54
The object 'Create_Database_Trigger' does not exist in database 'master' or is invalid for this operation.
I've done the typical things you'd expect like closing SSMS, reconnecting, even doing this, thinking the trigger was somehow not in master:
sp_msforeachdb 'SELECT ''?'' as db, * from [?].sys.triggers '
can anyone tell me where my triggers have wandered off to?
Server Info
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
10.0.4000.0
SP2
Standard Edition (64-bit)
Client Tools
Microsoft SQL Server Management Studio10.0.4000.0
Microsoft Analysis Services Client Tools10.0.4000.0
Microsoft Data Access Components (MDAC)6.1.7600.16385
Microsoft MSXML3.0 4.0 5.0 6.0
Microsoft Internet Explorer8.0.7600.16385
Microsoft .NET Framework2.0.50727.4963
Operating System6.1.7600
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Jack Corbett answered in another thread that prompted me to question my sanity.
it's the wrong view, but i still cannot use sp_helptext on a server trigger?
select * from sys.server_triggers
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I think You can not do sp_helptext on server level triggers. you can generate the script by ssms.
-
This is how SSMS generates the trigger text:
exec sp_executesql N'SELECT
NULL AS [Text],
ssmod.definition AS [Definition]
FROM
master.sys.server_triggers AS tr
LEFT OUTER JOIN master.sys.server_assembly_modules AS mod ON mod.object_id = tr.object_id
LEFT OUTER JOIN sys.server_sql_modules AS ssmod ON ssmod.object_id = tr.object_id
WHERE
(tr.parent_class = 100)and(tr.name=@_msparam_0)',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'test'
sp_helptext uses syscomments and it looks like server triggers aren't included there.
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 -
Try.. SELECT * FROM sys.server_triggers
I had the same problem and someone else helped me (today) on this forum.. happy turkey day!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply