Sponsored Content
Top Forums Programming MYSQL - trigger to track changes to fields on update Post 302891167 by barrydocks on Tuesday 4th of March 2014 08:06:12 AM
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
 

8 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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
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)
All times are GMT -4. The time now is 10:32 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy