Post reply

ddL TRIGGER not working

  • August 24, 2011 at 4:07 am

    #256072

    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

    #1372461

    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

    #1372495

    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

    #1372497

    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