Unable to view the triggers

Question

Post reply

Unable to view the triggers

sundar329

Say Hey Kid

Points: 680
More actions
August 16, 2013 at 4:48 am

#297000

Hi friends,
I have created trigger for login 'XXXX' in master database which does not exceeds 3 sessions. But am unable to see the trigger both in GUI or by query.
Trigger script is given below:
use master
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'login_test') > 3 -- only three sessions
ROLLBACK;
END;
I used select * from sys.triggers but am unable to see this trigger. Am unable to get the trigger name.
Kindly suggest a way to find a solution for the above problem.

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

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

Lowell SSC Guru Points: 323506 More actions · Answer 1

That tripped me up once too,

Sys.server_triggers is the view for database or server triggers. Sys.triggers is only those 8n views or tables

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!

sundar329 Say Hey Kid Points: 680 More actions · Answer 2

Thanks a lot Lowell... We found the name of the trigger with the view given by Lowell...

sundar329 Say Hey Kid Points: 680 More actions · Answer 3

Am using the below query to get the script of that trigger. But am unable to get the script of the trigger.

SELECT M.Definition as CreateScript

FROM sys.sql_modules as M INNER JOIN Sys.server_triggers as O

ON M.object_id = O.object_id

Is there any other way to get the script trigger. I tried through GUI but its not working.

sundar329 Say Hey Kid Points: 680 More actions · Answer 4

Finally used sys.server_sql_modules to get the script of the triggers created.