Creating a Trigger on Access

Hi,
I am trying to create a trigger to update a datetime field when a user
logs in to their account. Is there a way to create a trigger that
updates a field when the table is accessed? The only other possible
way I can think of to accomplish this would be to write code that
updates a field on submit so that it trips the trigger I have to update
the time. This does not seem especially efficient, though.I don't think a 'SELECT' trigger would be a good idea since data could be
access for reasons other than login. IMHO, a stored procedure would be the
best approach since you can include other login activities, like password
validation. For example:
CREATE PROCEDURE dbo.usp_LoginUser
@.UserName varchar(30),
@.Password varchar(30)
AS
SET NOCOUNT ON
DECLARE @.ReturnCode int
UPDATE dbo.Users
SET LastLoginTime = GETDATE()
WHERE
UserName = @.UserName AND
Password = @.Password
IF @.@.ROWCOUNT > 0
BEGIN
--indicate login success
SET @.ReturnCode = 0
END
ELSE
BEGIN
--indicate login failure
SET @.ReturnCode = 1
END
RETURN @.ReturnCode
GO
Hope this helps.
Dan Guzman
SQL Server MVP
<iamalex84@.gmail.com> wrote in message
news:1148414912.126785.4600@.j73g2000cwa.googlegroups.com...
> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||Thank you very much. I have customized and created the procedure. One
more problem though... How do I use it in ASP? I have never worked
with stored procedures before. I'm having trouble with passing the
username and password to it. Also, how do I retrieve the returned
values? Also, is there a way to grab the current value before it is
updated?
Dan Guzman wrote:[vbcol=seagreen]
> I don't think a 'SELECT' trigger would be a good idea since data could be
> access for reasons other than login. IMHO, a stored procedure would be th
e
> best approach since you can include other login activities, like password
> validation. For example:
> CREATE PROCEDURE dbo.usp_LoginUser
> @.UserName varchar(30),
> @.Password varchar(30)
> AS
> SET NOCOUNT ON
> DECLARE @.ReturnCode int
> UPDATE dbo.Users
> SET LastLoginTime = GETDATE()
> WHERE
> UserName = @.UserName AND
> Password = @.Password
> IF @.@.ROWCOUNT > 0
> BEGIN
> --indicate login success
> SET @.ReturnCode = 0
> END
> ELSE
> BEGIN
> --indicate login failure
> SET @.ReturnCode = 1
> END
> RETURN @.ReturnCode
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <iamalex84@.gmail.com> wrote in message
> news:1148414912.126785.4600@.j73g2000cwa.googlegroups.com...|||> Thank you very much. I have customized and created the procedure. One
> more problem though... How do I use it in ASP? I have never worked
> with stored procedures before. I'm having trouble with passing the
> username and password to it. Also, how do I retrieve the returned
> values? Also, is there a way to grab the current value before it is
> updated?
Below is a VBScript example as well as a proc that will return data via an
output parameter.
CREATE PROCEDURE dbo.usp_LoginUser
@.UserName varchar(30),
@.Password varchar(30),
@.LastLoginTime datetime OUTPUT
AS
SET NOCOUNT ON
DECLARE @.ReturnCode int
UPDATE dbo.Users
SET
@.LastLoginTime = LastLoginTime,
LastLoginTime = GETDATE()
WHERE
UserName = @.UserName AND
Password = @.Password
IF @.@.ROWCOUNT > 0
BEGIN
--indicate login success
SET @.ReturnCode = 0
END
ELSE
BEGIN
--indicate login failure
SET @.ReturnCode = 1
END
RETURN @.ReturnCode
GO
'see http://www.4guysfromrolla.com/webtech/110199-1.shtml for
'methods to include ADO constants in ASP
connection.Open connectionString
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandType = adCmdStoredProcedure
command.CommandText = "dbo.usp_LoginUser"
Set returnCodeParameter = command.CreateParameter( _
"@.ReturnCode", adInteger, adParamReturnValue)
command.Parameters.Append returnCodeParameter
Set userNameParameter = command.CreateParameter( _
"@.UserName", adVarChar, adParamInput, 30, Request("UserName"))
command.Parameters.Append userNameParameter
Set passwordParameter = command.CreateParameter( _
"@.Password", adVarChar, adParamInput, 30, Request("Password"))
command.Parameters.Append passwordParameter
Set lastLoginTimeParameter = command.CreateParameter( _
"@.LastLoginTime", adDBTimeStamp, adParamOutput)
command.Parameters.Append lastLoginTimeParameter
command.Execute()
If returnCodeParameter.Value = 0 Then
Response.Write "Login succeeded. Last login was " &
lastLoginTimeParameter.Value
Else
Response.Write "Login failed"
End If
connection.Close
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <iamalex84@.gmail.com> wrote in message
news:1149801803.857235.271770@.f6g2000cwb.googlegroups.com...
> Thank you very much. I have customized and created the procedure. One
> more problem though... How do I use it in ASP? I have never worked
> with stored procedures before. I'm having trouble with passing the
> username and password to it. Also, how do I retrieve the returned
> values? Also, is there a way to grab the current value before it is
> updated?
>
> Dan Guzman wrote:
>|||Ah, thank you so much. This is perfect.
Dan Guzman wrote:[vbcol=seagreen]
> Below is a VBScript example as well as a proc that will return data via an
> output parameter.
>
> CREATE PROCEDURE dbo.usp_LoginUser
> @.UserName varchar(30),
> @.Password varchar(30),
> @.LastLoginTime datetime OUTPUT
> AS
> SET NOCOUNT ON
> DECLARE @.ReturnCode int
> UPDATE dbo.Users
> SET
> @.LastLoginTime = LastLoginTime,
> LastLoginTime = GETDATE()
> WHERE
> UserName = @.UserName AND
> Password = @.Password
> IF @.@.ROWCOUNT > 0
> BEGIN
> --indicate login success
> SET @.ReturnCode = 0
> END
> ELSE
> BEGIN
> --indicate login failure
> SET @.ReturnCode = 1
> END
> RETURN @.ReturnCode
> GO
> 'see http://www.4guysfromrolla.com/webtech/110199-1.shtml for
> 'methods to include ADO constants in ASP
> connection.Open connectionString
> Set command = CreateObject("ADODB.Command")
> command.ActiveConnection = connection
> command.CommandType = adCmdStoredProcedure
> command.CommandText = "dbo.usp_LoginUser"
> Set returnCodeParameter = command.CreateParameter( _
> "@.ReturnCode", adInteger, adParamReturnValue)
> command.Parameters.Append returnCodeParameter
> Set userNameParameter = command.CreateParameter( _
> "@.UserName", adVarChar, adParamInput, 30, Request("UserName"))
> command.Parameters.Append userNameParameter
> Set passwordParameter = command.CreateParameter( _
> "@.Password", adVarChar, adParamInput, 30, Request("Password"))
> command.Parameters.Append passwordParameter
> Set lastLoginTimeParameter = command.CreateParameter( _
> "@.LastLoginTime", adDBTimeStamp, adParamOutput)
> command.Parameters.Append lastLoginTimeParameter
> command.Execute()
> If returnCodeParameter.Value = 0 Then
> Response.Write "Login succeeded. Last login was " &
> lastLoginTimeParameter.Value
> Else
> Response.Write "Login failed"
> End If
> connection.Close
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex" <iamalex84@.gmail.com> wrote in message
> news:1149801803.857235.271770@.f6g2000cwb.googlegroups.com...|||Ah, thank you so much. This is perfect.
Dan Guzman wrote:[vbcol=seagreen]
> Below is a VBScript example as well as a proc that will return data via an
> output parameter.
>
> CREATE PROCEDURE dbo.usp_LoginUser
> @.UserName varchar(30),
> @.Password varchar(30),
> @.LastLoginTime datetime OUTPUT
> AS
> SET NOCOUNT ON
> DECLARE @.ReturnCode int
> UPDATE dbo.Users
> SET
> @.LastLoginTime = LastLoginTime,
> LastLoginTime = GETDATE()
> WHERE
> UserName = @.UserName AND
> Password = @.Password
> IF @.@.ROWCOUNT > 0
> BEGIN
> --indicate login success
> SET @.ReturnCode = 0
> END
> ELSE
> BEGIN
> --indicate login failure
> SET @.ReturnCode = 1
> END
> RETURN @.ReturnCode
> GO
> 'see http://www.4guysfromrolla.com/webtech/110199-1.shtml for
> 'methods to include ADO constants in ASP
> connection.Open connectionString
> Set command = CreateObject("ADODB.Command")
> command.ActiveConnection = connection
> command.CommandType = adCmdStoredProcedure
> command.CommandText = "dbo.usp_LoginUser"
> Set returnCodeParameter = command.CreateParameter( _
> "@.ReturnCode", adInteger, adParamReturnValue)
> command.Parameters.Append returnCodeParameter
> Set userNameParameter = command.CreateParameter( _
> "@.UserName", adVarChar, adParamInput, 30, Request("UserName"))
> command.Parameters.Append userNameParameter
> Set passwordParameter = command.CreateParameter( _
> "@.Password", adVarChar, adParamInput, 30, Request("Password"))
> command.Parameters.Append passwordParameter
> Set lastLoginTimeParameter = command.CreateParameter( _
> "@.LastLoginTime", adDBTimeStamp, adParamOutput)
> command.Parameters.Append lastLoginTimeParameter
> command.Execute()
> If returnCodeParameter.Value = 0 Then
> Response.Write "Login succeeded. Last login was " &
> lastLoginTimeParameter.Value
> Else
> Response.Write "Login failed"
> End If
> connection.Close
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex" <iamalex84@.gmail.com> wrote in message
> news:1149801803.857235.271770@.f6g2000cwb.googlegroups.com...sql