SQL AGENT Alert on Logon Trigger Raiserror

  • I have a Logon Trigger...fires a raiserror and I want an alert to fire when it does to send an email.

    ground rules:

    1. I know logon triggers that are not explicitly AFTER, fire after authentication and before connection...therefore the raiserror does not get to the USER but goes to the SQL Log. Thats OK with me. I know that.

    2. I dont want an AFTER logon trigger.

    3. Alert defined on all-databases does not work with this raiserror but "within-connection raiserror" does fire the Alert ...so my alert is not inherently at fault. Very simple.

    4. If you think its just not possible because of #1 above...say so. Thats where I'm at....but would like to see a novel solution from you smart people out there.

    5. Do not want to execute smtp sendmail from the trigger.

    The thing that perplexes me is the SQL Log gets the error message .. so there must be some type of event happening.

    thanks i advance,

    John H.

  • I think I found an answer to my own question. I need to create an Event-Notification (what sql agent Alerts use under cover)...create a QUEUE,SERVICE,ROUTE, EVENT NOTIFICATION...or if not an EVENT, then Issue a message (after rollback) to the queue from my logon trigger and have the queue ACTIVATION kick off an email or something.

    might also work for notifying the user with a spcific message rather then the generic trigger error that they get now when I Rollback.

Viewing 2 posts - 1 through 1 (of 1 total)

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