Re: Creating a Trigger on Access

Ah, thank you so much. This is perfect.


Dan Guzman wrote:
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@xxxxxxxxx> wrote in message
news:1149801803.857235.271770@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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:
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@xxxxxxxxx> wrote in message
news:1148414912.126785.4600@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.



.

Relevant Pages

  • Re: Creating a Trigger on Access
    ... with stored procedures before. ... username and password to it. ... DECLARE @ReturnCode int ... --indicate login success ...
    (microsoft.public.sqlserver.security)
  • Re: Creating a Trigger on Access
    ... username and password to it. ... access for reasons other than login. ... DECLARE @ReturnCode int ... I am trying to create a trigger to update a datetime field when a user ...
    (microsoft.public.sqlserver.security)
  • Re: Creating a Trigger on Access
    ... I don't think a 'SELECT' trigger would be a good idea since data could be ... access for reasons other than login. ... a stored procedure would be the ... DECLARE @ReturnCode int ...
    (microsoft.public.sqlserver.security)