473,320 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Automagically log changes in table

This is more in the context of Turbogears/SQLAlchemy, but if anyone
has implemented something similar with other packages it might be
useful to know.

I'd like to have a way to make a table "loggable", meaning it would
get, say, two fields "last_modified" and "modified_by", and every
write operation on it would automatically record the time and the id
of the user who did the addition or change (I'm not sure how to deal
with deletions let's leave this for now). Has anyone done something
like that or knows where to start from ?

George

Mar 17 '07 #1
5 1138
Hi

You can get this using "triggers" and "stored procedures".
These are SQL engine dependent! This is available for long time with
postgress and only from version 5 with mysql.
This let you write SQL code (Procedure) that will be called when
"trigged" by an event like inserting new row, updating rows,
deleting ....

BR
On 17 mar, 21:43, "George Sakkis" <george.sak...@gmail.comwrote:
This is more in the context of Turbogears/SQLAlchemy, but if anyone
has implemented something similar with other packages it might be
useful to know.

I'd like to have a way to make a table "loggable", meaning it would
get, say, two fields "last_modified" and "modified_by", and every
write operation on it would automatically record the time and the id
of the user who did the addition or change (I'm not sure how to deal
with deletions let's leave this for now). Has anyone done something
like that or knows where to start from ?

George

Mar 18 '07 #2
On Mar 17, 7:59 pm, "aspineux" <aspin...@gmail.comwrote:
Hi

You can get this using "triggers" and "stored procedures".
These are SQL engine dependent! This is available for long time with
postgress and only from version 5 with mysql.
This let you write SQL code (Procedure) that will be called when
"trigged" by an event like inserting new row, updating rows,
deleting ....
I'd rather avoid triggers since I may have to deal with Mysql 4. Apart
from that, how can a trigger know the current user ? This information
is part of the context (e.g. an http request), not stored persistently
somewhere. It should be doable at the framework/orm level but I'm
rather green on Turbogears/SQLAlchemy.

George

Mar 18 '07 #3
George Sakkis schrieb:
This is more in the context of Turbogears/SQLAlchemy, but if anyone
has implemented something similar with other packages it might be
useful to know.
SQLObject since version 0.8 lets you define event listeners for
create/update/delete events on objects. You could hook into them.

Diez
Mar 18 '07 #4
On 18 mar, 04:20, "George Sakkis" <george.sak...@gmail.comwrote:
On Mar 17, 7:59 pm, "aspineux" <aspin...@gmail.comwrote:
Hi
You can get this using "triggers" and "stored procedures".
These are SQL engine dependent! This is available for long time with
postgress and only from version 5 with mysql.
This let you write SQL code (Procedure) that will be called when
"trigged" by an event like inserting new row, updating rows,
deleting ....

I'd rather avoid triggers since I may have to deal with Mysql 4. Apart
from that, how can a trigger know the current user ?
Because each user opening an web session will login to the SQL
database using it's own
login. That way you will be able to manage security at DB too. And the
trigger
will use the userid of the SQL session.
This information
is part of the context (e.g. an http request), not stored persistently
somewhere. It should be doable at the framework/orm level but I'm
rather green on Turbogears/SQLAlchemy.

George
Maybe it will be easier to manage this in your web application.

it's not too dificulte to replace any

UPDATE persone
SET name = %{new_name}s
WHERE persone_id=%{personeid}

by something like

UPDATE persone
SET name = %{new_name}s, modified_by=%{user_id}s, modified_time=%
{now}s
WHERE persone_id=%{personeid}

BR

Mar 18 '07 #5
"aspineux" <as******@gmail.comwrites:
On 18 mar, 04:20, "George Sakkis" <george.sak...@gmail.comwrote:
>>
I'd rather avoid triggers since I may have to deal with Mysql 4. Apart
from that, how can a trigger know the current user ?

Because each user opening an web session will login to the SQL database
using it's own login. That way you will be able to manage security at DB
too. And the trigger will use the userid of the SQL session.
That's not common in web applications (he mentions TurboGears later). What is
common is having a connection pool and just asking for one of the available
connections, when your app gets it, it just uses it. After each request this
connection returns to the pool to be reused.
I dunno about SQL Alchemy (also mentioned later), but SQL Object 0.8x has some
events that can be bound so they can act is triggers on your database, but
client side. Of course they don't have all the context as a real trigger
does, but those might be enough to avoid duplicating lots of code through the
app to set some variable.

--
Jorge Godoy <jg****@gmail.com>
Mar 18 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Ken Wilkins | last post by:
Is there a way to add a field to a table when the same one is created in another table? I'm working with keeping track of software for different versions of OS. If I add a field to the Win98...
5
by: keith.culpepper | last post by:
Can anyone please provide some assistance with a trigger that I need to develop. Here is the situation: Our program updates depend on database updates. If a client receives the program update...
6
by: Ian Boyd | last post by:
Every time during development we had to make table changes, we use Control Center. Most of the time, Control Center fails. If you try to "undo all", it doesn't, and you end up losing your identity...
2
by: grinder332518 | last post by:
My table is called "table A". There is a date field in this table called "1st Date Issued". My Form accesses this table, and I would like to write an expression which looks at all the entries in...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.