August 16, 2013 at 4:48 am
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.
August 16, 2013 at 4:52 am
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
August 16, 2013 at 5:06 am
Thanks a lot Lowell... We found the name of the trigger with the view given by Lowell...
August 16, 2013 at 5:28 am
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.
August 16, 2013 at 6:51 am
Finally used sys.server_sql_modules to get the script of the triggers created.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy