Beginner Question - Monitor users on Database 1

[nway]

Hi our firm just implemented a new CRM and they would like to know how often people are logging in a month. I'm extremely new to SQL server 7.0 and I was hoping some one could point me in the right direction. The Database has a last login column but I need to find a way to add each instance a user initiates. Is there a stored proceedure or log that I can go to or do I have to create one? Also if you could recommend a good beginner SQL 7.0 book I'd appreciate it.

Thanks

 

[jamesdavies]

You should be able to create a teble to store all logins, then add a trigger tp the table with the last_login column to populate it:

create table login_history ( staff_id int NOT NULL, logged_in datetime NOT NULL)
GO

create trigger a_name on a_table for update AS

if update (last_login)
   begin
       insert into login_history (staff_id, logged_in)
       select staff_id, last_login from inserted
   end

- inserted is a "shadow" table available to triggers witht eh same definition as the table the trigger is on - it contains new or modified rows.
- check whether there is an existing update trigger on the table - you will probably be better off adding thsi code to it if there is.
- trigger must have a unique name, though its only used to drop it.

 

[nway]

Thanks for your help I knew that a trigger was involved and now you have confirmed I was on the right path.