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
8 More Discussions You Might Find Interesting
1. UNIX for Dummies Questions & Answers
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
2. UNIX and Linux Applications
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
3. Shell Programming and Scripting
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
4. UNIX for Dummies Questions & Answers
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
5. Shell Programming and Scripting
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
6. UNIX for Dummies Questions & Answers
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
7. Programming
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
8. Programming
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
LEARN ABOUT DEBIAN
tangram::type::hash::scalar
Tangram::Type::Hash::Scalar(3pm) User Contributed Perl Documentation Tangram::Type::Hash::Scalar(3pm)
NAME
Tangram::Type/Hash/Scalar - map Perl hash of scalar keys and values
SYNOPSIS
use Tangram::Core;
use Tangram::Type/Hash/Scalar; # always
$schema = Tangram::Schema->new(
classes => { NaturalPerson => { fields => {
flat_hash =>
{
opinions =>
{
table => 'NP_ops',
key_sql => 'VARCHAR(10)',
type => 'int',
sql => 'NUMERIC(1)',
},
lucky_numbers => 'int', # use defaults
}
DESCRIPTION
Maps references to a Perl hash. The persistent fields are grouped in a hash under the "flat_hash" key in the field hash.
The hash may contain as keys and values only 'simple' scalars like integers, strings or real numbers. It may not contain references. For
hashs of objects, see Tangram::Type::Hash::FromMany and Tangram::Type::Hash::FromOne.
Tangram uses a table to save the state of the collection. The table has three columns, which contain
* the id of the container object
* the key of the element in the hash
* the value of the element
The field names are passed in a hash that associates a field name with a field descriptor. The field descriptor may be either a hash or a
string. The hash uses the following fields:
* key_type
* key_sql
* type
* sql
* table
The optional fields "key_type" and "type" specify the key and value types of the hash. If the type is "string" Tangram quotes the values as
they are passed to the database. Not specifying a "type" is exactly equivalent to specifying "string".
Optional field "table" sets the name of the table that contains the elements. This defaults to 'C_F', where C is the class of the contain-
ing object and F is the field name.
The optional fields "key_sql" and "sql" specify the type that deploy() (see Tangram::Deploy) should use for the column containing the key
and value of the hash. If this field is not present, the SQL type is derived from the "type" field: if "type" is "string" (or is absent)
VARCHAR(255) is used; otherwise, the "type" field is interpreted as a SQL type.
If the descriptor is a string, it is interpreted as the value of the "type" field and all the other fields take the default value.
AUTHOR
This mapping was contributed by Gabor Herr <herr@iti.informatik.tu-darmstadt.de>
perl v5.8.8 2006-03-29 Tangram::Type::Hash::Scalar(3pm)