MYSQL - trigger to track changes to fields on update


 
Thread Tools Search this Thread
Top Forums Programming MYSQL - trigger to track changes to fields on update
# 1  
Old 03-04-2014
MYSQL - trigger to track changes to fields on update

So I have a php web application that allows updating of records. I would like to track the changes of only the fields that have changed. There are plenty of how to's that involve recreating the original row in a separate table with an additional time/date stamp column but I think this is a big waste of space.

What I would like to achieve is a changelog table:
id - INT - NOT NULL - AUTO INCREMENT
FK_id -INT - NOT NULL //PK of the row in the original table
changed_field_name - VARCHAR - NOT NULL
old_value - VARCHAR - NULL //maybe an empty field
new_values - VARCHAR - NOT NULL
timestamp - YYYY-MM-DD:HH:MM:SS
FK_user_id - INT - NOT NULL //PK from the user table

The data in the original table consists of various formats including text, varchar, dates, integers - several fields may be updated at the same time, in which case several rows will need to be added to the changelog table. The changelog table will only be used to display the changed data in a form.

I think the best way to achieve this is with a trigger?

any help would be welcome.
Thanks
# 2  
Old 03-12-2014
Yes, a trigger can preserve the entire row with an end time stamp of when it was removed, removing id, update or delete flag, ... in a side table. Trigger on update and delete. Take it all at first, in case your scope expands later. Naturally, this slows your churn. Having an insert date in your rows is a nice companion to the end date.

You can also do it all in one table, where there is an underlying, unified history table and a current 'status' or 'active' view that hides older rows or old and deleted rows. The rows do not ever get updated or dleted, you have 100% history, and you only insert.
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Programming

How to track table status delete/update/insert status in DB2 V10 z/os?

Dear Team I am using DB2 v10 z/os database . Need expert guidance to figure out best way to track table activities ( Ex Delete, Insert,Update ) Scenario We have a table which is critical and many developer/testing team access on daily basis . We had instance where some deleted... (1 Reply)
Discussion started by: Perlbaby
1 Replies

2. Programming

MYSQL - trigger update on record insert or update

Right I have a MYSQL database with table1 with 3 columns, colA, colB and colC. I want to combine the data in the 3 columns into a 4th column names col_comb. Here's the SQL command that works: UPDATE table1 SET `col_comb` = CONCAT( `colA` , ' - ', `colB` , ', ', `colC` ); So now I want this... (5 Replies)
Discussion started by: barrydocks
5 Replies

3. UNIX for Dummies Questions & Answers

Mysql: How to update value in 27000 rows?

Hello, some member created 27000 posts in wrong section (lol :D) so i need to edit all his entries to get new section ID. SELECT * FROM `phpbb_topics` WHERE `topic_first_poster_name` LIKE "%ozerway%"; this will select all his topics... the column with forum id is named "forum_id" and... (3 Replies)
Discussion started by: postcd
3 Replies

4. Shell Programming and Scripting

Update a mysql column via bash script

Hello, I want to check the value of all MySQL columns.(column name is "status") via bash script. If value is "0" at I want to make only single column value to "1" I have many "0" values on mysql database(on "status" column) "0" means it is a draft post. I want to publish a post. I... (2 Replies)
Discussion started by: tara123
2 Replies

5. UNIX for Dummies Questions & Answers

Match the amount fields in the source file vs trigger file

Hello, I have to write a script to compare the sum of the amount fields in a source file and the amount field in another file. details are: based on the name of the source file (say SALES as an example), a file already available in a path will be grabbed (say SALES_ParmFile) and this file... (4 Replies)
Discussion started by: vijaylak
4 Replies

6. Shell Programming and Scripting

Update trigger for unix file (solaris)

Hello, from log error file of process that i's updating in append mode, i need to capture the new entries for every day. How i can know and save only the new errors? This it's a sample of error log file (oas report server engine) thanks and regards Fran (2 Replies)
Discussion started by: fran61
2 Replies

7. UNIX and Linux Applications

update trigger

hi all, i hope i am posting this /beginner) question in the right forum: i want to create an update trigger, which rolls back a transaction if a record of a table is updated. the table has - amongst others - a field 'statusid' - if a record in this table has the statusid X and it is attempted... (0 Replies)
Discussion started by: kalinkula
0 Replies

8. UNIX for Dummies Questions & Answers

Possible to track FTP user last login? Last and Finger don't track them.

Like the topic says, does anyone know if it is possible to check to see when an FTP only user has logged in? Because the shell is /bin/false and they are only using FTP to access the system doing a "finger" or "last" it says they have never logged in. Is there a way to see when ftp users log in... (1 Reply)
Discussion started by: LordJezo
1 Replies
Login or Register to Ask a Question