we occasionally have issues where someone tries to login with someone elses cashier ID. i put together a way of tracking which workstation the logon attempt was made from, and when it was made, and thought i would share it with whomever would like to see it. first i created a new table:
CREATE TABLE [c_CaptureFailedLogons] ( [ID] [int] IDENTITY (1,1) NOT NULL, [CashierID] [int] NOT NULL, [ClientComputerName] nvarchar(30) NOT NULL, [Comments] nvarchar(30) NOT NULL DEFAULT (''), [Time] [datetime] NOT NULL, [DBTimeStamp] [timestamp] NULL )
then i created an UPDATE trigger on the Cashier table:
CREATE TRIGGER tr_LoginAttempt ON Cashier FOR UPDATE AS IF @@ROWCOUNT = 0 RETURN
IF (SELECT FailedLogonAttempts FROM INSERTED) > 0 BEGIN DECLARE @Cashier INT SELECT @Cashier = Number FROM INSERTED INSERT INTO c_CaptureFailedLogons (CashierID, ClientComputerName, Time) VALUES (@Cashier, Host_Name(), GetDate()) END
it inserts a new record into the new table, whenever there is a change to one of the records in the cashier table but only when the change to the table changes the FailedLogonAttempts column value to something other than zero.
kevin