Hi, does anyone know if it's possible for a timestamp column to
automatically update itself when a select is made? We have a
user/password/roles database that is accessed by Tomcat's j_security =
module
whenever a user logs into one of our webapps, and I want to know which =
users
haven't logged in recently so that I can erase their accounts. I would
appreciate all of your feedback on this one.

Thanks!

Paulo Urcid Pliego
E-Labor Producci=F3n DM e IM
Preserie y Construcci=F3n de Equipos

Volkswagen de M=E9xico S.A. de C.V.
Tel=E9fono +52 (222) 2 30 9961
Fax +52 (222) 2 30 6082
mailto:urcid [at] vw.com.mx
http://www.vw.com.mx

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32 [at] m.gmane.org

Paulo,

In general, the way it works is by having a first TIMESTAMP column which
can be NULL and have NULL as their default value, and arranging to
update that row with a NULL or simply in place and having the default
NULL trigger the update. Beginning with 4.1 there are other options:

http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html

Given a version of MySQL sufficiently advanced (5+) you can use triggers
to do this automatically.

There's a drill showing how this works at:

http://www.huggle.com/forums/viewtopic.php?t=3D38&sid=3Dc7f9 6ce4daf69c90c=
50a451b51067a40


- Jan


On Sat, 1 Apr 2006 14:24:57 -0600 , "Urcid Pliego, Paulo"
<paulo.urcid [at] vw.com.mx> said:
> Hi, does anyone know if it's possible for a timestamp column to
> automatically update itself when a select is made? We have a
> user/password/roles database that is accessed by Tomcat's j_security
> module whenever a user logs into one of our webapps, and I want to
> know which users haven't logged in recently so that I can erase their
> accounts. I would appreciate all of your feedback on this one.
>
> Thanks!
>

[snip]
--
Jan Theodore Galkowski (o=B0) =

jtgalkowski [at] alum.mit.edu
http://www.smalltalkidiom.net



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32 [at] m.gmane.org

--=_alternative 00161CB785257145_=
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable

"Urcid Pliego, Paulo" <paulo.urcid [at] vw.com.mx> wrote on 04/01/2006 03:24:57 =

PM:

> Hi, does anyone know if it's possible for a timestamp column to
> automatically update itself when a select is made? We have a
> user/password/roles database that is accessed by Tomcat's j=5Fsecurity
module
> whenever a user logs into one of our webapps, and I want to know which
users
> haven't logged in recently so that I can erase their accounts. I would
> appreciate all of your feedback on this one.
>
> Thanks!
>
> Paulo Urcid Pliego
> E-Labor Producci=F3n DM e IM
> Preserie y Construcci=F3n de Equipos
>
> Volkswagen de M=E9xico S.A. de C.V.
> Tel=E9fono +52 (222) 2 30 9961
> Fax +52 (222) 2 30 6082
> mailto:urcid [at] vw.com.mx
> http://www.vw.com.mx
>

Every SQL standard that I have read does NOT allow for read operations
(such as SELECT statements) to affect the data being read. It would create =

a very chaotic situation if it were possible to read the same row of data
twice in a row and get different responses, wouldn't it?

In order to change a record, you need to try to UPDATE it. The act of
logging in is sufficient justification for me to run an UPDATE statement
to change the last login datetime value to something else. Were you just
trying to get two actions with one statement?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


--=_alternative 00161CB785257145_=--

I am pretty sure you will need to make your client software do any =
updating you require. Depending on the version of MySQL you are using =
you may be able to achieve what you want by using a stored procedure =
that updates the timestamp when you read the login information. Using a =
stored procedure would shift most of the changes to the server.

John B.

-----Original Message-----
From: Urcid Pliego, Paulo [mailto:paulo.urcid [at] vw.com.mx]
Sent: Sunday, 2 April 2006 5:55 AM
To: win32 [at] lists.mysql.com
Subject: Update timestamp with a select

Hi, does anyone know if it's possible for a timestamp column to
automatically update itself when a select is made? We have a
user/password/roles database that is accessed by Tomcat's j_security =
module
whenever a user logs into one of our webapps, and I want to know which =
users
haven't logged in recently so that I can erase their accounts. I would
appreciate all of your feedback on this one.

Thanks!

Paulo Urcid Pliego
E-Labor Producci=F3n DM e IM
Preserie y Construcci=F3n de Equipos

Volkswagen de M=E9xico S.A. de C.V.
Tel=E9fono +52 (222) 2 30 9961
Fax +52 (222) 2 30 6082
mailto:urcid [at] vw.com.mx
http://www.vw.com.mx


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32 [at] m.gmane.org