0

Is it possible to create a trigger so that works with the following scenarios:

  • User types valid login info to the web application
    • A trigger is activated and login name is inserted to the audit table, along with timestamp

and

  • User types invalid login info
    • Trigger is activated and user name is also saved in the audit table along with timestamp
1

Your best option here is to create a Stored procedure to handle everything.

On user login, the stored procedure is called with the username and password(Encrypted) for example.

This stored procedure would do the following

  1. Validate the login.
  2. Update a Lastlogin timestamp
  3. write the login audit row (irrespective of successful or unsuccessful login.

This would be the best way to handle the process. I would then wrap the above in a transaction, to ensure that the audit record is always created for any login. If the transaction fails, the user will have to login again.

Here is a very basic example for the sake of keeping it simple. It can definitely be improved on for your specific use case, but this is broken down for easy reading and logic, especially around the password validation as encryption should be used but is not for this example :)

CREATE PROCEDURE ApplicationLogin @Username nvarchar(255), @Password nvarchar(255), @IpAddress
AS
BEGIN
   DECLARE @UserID int;
   BEGIN TRANSACTION;  

   SET @UserID = (SELECT UserID FROM Logins WHERE Username = @Username AND Passwword = @Password );

   IF @UserID > 0
   BEGIN
     UPDATE Logins SET LastLogin = GETDATE() WHERE UserID = @UserID;
   END

   INSERT INTO UserAudit (Username, UserID, Password, IpAddress, TimeStamp)
   Values(@Username, @UserID, @Password, @IpAddress, @Timestamp);

   COMMIT TRANSACTION;

   SELECT @UserID --On your client, check if the returned @UserID is >0. If it is, then successful login. If not, then unsuccessful.

END

The login check and LastLogin Update could probably be combined. You could also use a try catch instead of a transaction. In this case I believe the difference in negligible. Also, as mentioned, you would want to have your passwords encrypted. What i liked to do, was encrypt the password in the client, and only compare encrypted strings. That means you never need to decrypt the password. Its always encrypted and therefore i believe is more secure.

  • Can you please provide an example of a stored porcedure for a simple login? – Mindan Jan 22 at 15:58
  • I edited the question with an example of a trigger I found on line but it works only with edit operations not with login attempts – Mindan Jan 22 at 16:07
  • @Mindan Looks like you tried to edit Wade's answer, not the question. – LowlyDBA Jan 22 at 17:05
  • Yes, I see that, I will try to redo the changes. sorry for the inconvenience – Mindan Jan 22 at 17:46
  • It does not seem like I can undo the changes in Wade's answer, I'm very sorry for that – Mindan Jan 22 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.