August 24, 2011 at 4:07 am
HI,
I am in the process of creating some DDl trigers on my sql 2008 R2 default instance.
My drop_Login and Create_login triggers work fine but for some reason I cannot get ALTER_LOGIN to work my code is below
create TRIGGER [ddl_tr_alt_Login]
ON ALL SERVER
FOR ALTER_LOGIN
AS
declare @results varchar(max)
SET @results = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'sql mail profile',
@recipients = 'me@here.com',
@body = @results,
@subject = 'Login Altered'
I have serached MSDN and BOL but cannot find a reason for this. Nothing in the logs.
Thanks
Ajb
August 24, 2011 at 4:59 am
Hi,
The code seems valid and works fine on my system, try printing @results from the trigger to check if there is any issue while sending mail.
Vishal Gajjar
http://SqlAndMe.com
August 24, 2011 at 6:10 am
Vishal
Good thinking, i got a message about quoted identifyers being inorrect so I changed the script to inculde the following
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
and it worked. thank you
August 24, 2011 at 6:10 am
Sorry,
I changed them to
SET ANSI_NULLS On
GO
SET QUOTED_IDENTIFIER On
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply