I am trying to create a trigger that will fire once for each user as they connect and disconnect.  Any suggestions?

Since you are dealing with the sqlserver and not with data , the only way i see you can do this is using @@CONNECTIONS if you only care to know the load , or go for it with sp_who

I'm assuming here you are using SQLServer 2000 and have no control over the querys launched against the sqlserver.

You can program a job that gets the result of sp_who into a table , compare the users with the last run of the job and if there is a difference , fire a sp where you do whatever you want to do (log in/out times)

This has some drawbacks , of course. Depending on the programming of the job , you can load too much the server if you program it to fire too continuosly , or you can miss enters and exits if they are quicker than your job. But is easy and doable.

HTH

---

Hey , sysadm , leave the users alone
They are nothing more
than another brick in the firewall

I am actually using SQLServer 2005 does that make a difference? Sorry, new to MS SQL. 

EW

 

Don't know if SQL2005 has some more facilities , just that SQL2000 is the one i'm more confortable and you didn't specify.

Anyhow , i googled a little a found something you could use if you go for the job option

http://www.sqlserver.org.au/resources/ViewResource.aspx?resourceId=8

more or less what i told you , but well written , documented and with the complete code :)

---

Hey , sysadm , leave the users alone
They are nothing more
than another brick in the firewall

I am sorry for the basic nature of this question, but after reviewing the example you provided I decided to try a simple stored procedure that I can call from my program.  I have sysadmin rights to this test db but when I save the stored procedure it does not appear in Management studieo anywhere.

Where am I messing up?

EW

 

Never mind I figured it out.  I needed to execute the stored procedure to create it.

Thanks.

EW

It’s much easier to catch login event in sql 2005

Review following document

http://msdn.microsoft.com/en-us/library/bb326598(SQL.90).aspx

 

That is what I needed to know thanks.

nice link , thanks for sharing , i'll surely use it sometime soon :)

---

Hey , sysadm , leave the users alone
They are nothing more
than another brick in the firewall