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
=20
Volkswagen de M=E9xico S.A. de C.V.
Tel=E9fono +52 (222) 2 30 9961
Fax +52 (222) 2 30 6082
mailto:urcid@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@m.gmane.org

Re: Update timestamp with a select

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"
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]
--=20
Jan Theodore Galkowski (o°) =

jtgalkowski@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@m.gmane.org

Re: Update timestamp with a select

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

"Urcid Pliego, Paulo" 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=20
module
> whenever a user logs into one of our webapps, and I want to know which=20
users
> haven't logged in recently so that I can erase their accounts. I would
> appreciate all of your feedback on this one.
>=20
> Thanks!
>=20
> Paulo Urcid Pliego
> E-Labor Producci=F3n DM e IM
> Preserie y Construcci=F3n de Equipos
>=20
> Volkswagen de M=E9xico S.A. de C.V.
> Tel=E9fono +52 (222) 2 30 9961
> Fax +52 (222) 2 30 6082
> mailto:urcid@vw.com.mx
> http://www.vw.com.mx
>=20

Every SQL standard that I have read does NOT allow for read operations=20
(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=20
twice in a row and get different responses, wouldn't it?=20

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

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


--=_alternative 00161CB785257145_=--

RE: Update timestamp with a select

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@vw.com.mx]=20
Sent: Sunday, 2 April 2006 5:55 AM
To: win32@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
=20
Volkswagen de M=E9xico S.A. de C.V.
Tel=E9fono +52 (222) 2 30 9961
Fax +52 (222) 2 30 6082
mailto:urcid@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@m.gmane.org