Does the title of this post trigger your mind? If you all remember, a few days back I had written an article on my interesting observation regarding logon triggers. I would advise you to first read SQL SERVER – Interesting Observation of Logon Trigger On All Servers before continuing with this article further to have a complete idea of the subject.

The question I put forth in my previous article was – In single login why the trigger fires multiple times; it should be fired only once. I received numerous answers in thread as well as in my MVP private news group. Now, let us discuss the answer for the same.

The answer is – It happens because multiple SQL Server services are running as well as intellisense is turned on.

Let us verify the above answer.

First, run the following script to create database and logon Audit table.

Hi All ,
I want to create a trigger that execute every day without any evet like without any DML command can i make this type of Trigger. i search from internet but i cannot get the answer. waiting for reply.

hi all,
i need to create trigger that execute when update or add data.that trigger must separate the data to different type of age for example. and make that data to text document.how to do it…please help me…

you have bounded these two tables in such a way that there is no point to insert the first records.
To insert records in these table at least one condition must be fulfilled:
1. Allow null in foreigk key column in any one table
2. Alter the foreign key constraint to NOCHECK in any one table
3. At least one record exists in any one table.

hi, i got problem. I was create trigger logon, but the reference of database don’t exists in trigger. So, now trigger generate error at the login in SQL and i can’t login at SQL Server 2008 R. How can i do, for login and disable trigger logon.

i have fired the above query and then close the sql server . But the problem arises when i tried again to logon that server, this gives me an error “LOGIN FAILED DUE TO THE EXECUTION OF TRIGGER”. please give me any suggestion how can i login on that server .

Hi Pinal, As I created Trigger as per your post, Just after 10 minutes, My site stopped working, And even no user was able to connect it neither remotely nor me locally, I used SQL CMD to connect my db & drop trigger, restart SQL Services, reset IIS and then everything was well. I want to know, What operation is performed by this trigger as my website stopped working. Even I had a lot of space on my drive.

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.