MYSQL - trigger to track changes to fields on update Post: 302891167

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

03-04-2014

Registered User

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

barrydocks

View Public Profile for barrydocks

Find all posts by barrydocks

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

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

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

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

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

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

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

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

LEARN ABOUT CENTOS

alter_trigger

ALTER 
TRIGGER(7)					  PostgreSQL 9.2.7 Documentation					  ALTER TRIGGER(7)

NAME

       ALTER_TRIGGER - change the definition of a trigger

SYNOPSIS

       ALTER TRIGGER name ON table_name RENAME TO new_name

DESCRIPTION

       ALTER TRIGGER changes properties of an existing trigger. The RENAME clause changes the name of the given trigger without otherwise changing
       the trigger definition.

       You must own the table on which the trigger acts to be allowed to change its properties.

PARAMETERS

       name
	   The name of an existing trigger to alter.

       table_name
	   The name of the table on which this trigger acts.

       new_name
	   The new name for the trigger.

NOTES

       The ability to temporarily enable or disable a trigger is provided by ALTER TABLE (ALTER_TABLE(7)), not by ALTER TRIGGER, because ALTER
       TRIGGER has no convenient way to express the option of enabling or disabling all of a table's triggers at once.

EXAMPLES

       To rename an existing trigger:

	   ALTER TRIGGER emp_stamp ON emp RENAME TO emp_track_chgs;

COMPATIBILITY

       ALTER TRIGGER is a PostgreSQL extension of the SQL standard.

SEE ALSO

       ALTER TABLE (ALTER_TABLE(7))

PostgreSQL 9.2.7						    2014-02-17							  ALTER TRIGGER(7)