MySQL

tracing value changes in my tables

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Nov 2008

Posts: 19

Reputation: Sally123 is an unknown quantity at this point

Solved Threads: 0

Sally123

Offline

Newbie Poster

tracing value changes in my tables

Dec 9th, 2008

hi

I am learning mysql and have created a table for auditing purposes with fields below , but my problems is I dont know how to go about inserting data into it whenever an event ocurs on my primary tables.especially getting the oldvalue,newvalue,column and table name fields after updates are done on my table

I need a purely simple trigger script that can update this table whenever my primary table gets updated by the users.

these are the only fields that I need for auditing purposes.

HR_Audit_Module table
AuditID(PK)
Table_Name,
tblRow_Id ----this is the ID from the table that gets updated to identify the column name,
Updated_By,
Update_when,
Old_Value,
New_Value,
FieldName (column name),
Operation_Type -- this should be --(update,insert or delete)

thanks in advance

•

Join Date: Nov 2008

Posts: 823

Reputation: verruckt24 is a jewel in the rough

Solved Threads: 73

verruckt24 verruckt24 is offline

Offline

Practically a Posting Shark

Re: tracing value changes in my tables

Dec 10th, 2008

If it is purely simple, you may as well write it on your own and ask us specific questions where you get stuck. We are not going to write the script for you.
PS : You should write it on your own even if it is not purely simple

Get up every morning and take a look at the Forbes' list of richest people. If your name doesn't appear.... GET TO WORK !!!

•

Join Date: Nov 2008

Posts: 19

Reputation: Sally123 is an unknown quantity at this point

Solved Threads: 0

Sally123

Offline

Newbie Poster

Re: tracing value changes in my tables

Dec 10th, 2008

•

Originally Posted by verruckt24

If it is purely simple, you may as well write it on your own and ask us specific questions where you get stuck. We are not going to write the script for you.
PS : You should write it on your own even if it is not purely simple

-------------------------
here is something that I have tried or have in my mind on how things should work but I am failing. the thing is I dont want to monitor specific field but all the fields in my table as per the audit table above .

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
CREATE trigger mytest
after UPDATE,DELETE
on mytest
AS
 
DECLARE @field_name VARCHAR(50), @OldValue VARCHAR(50), @NewValue VARCHAR(50)
 
if UPDATE
BEGIN
 
SELECT @field_name = column_Name
 
SELECT @OldValue = columnname() FROM deleted
 
SELECT @newValue = columnname() FROM updated
 
INSERT INTO Audit(Old_Value,NewValue ,Update_by,Updated_When,tblName,field_Name)
SELECT @OldValue,@NewValue,user(),CURRENT_DATE(),'mytest',@fieldName FROM mytest;
create trigger mytest
after update,delete
on mytest
AS

Declare @field_name varchar(50), @OldValue varchar(50), @NewValue varchar(50)

if update
begin

select @field_name = column_Name

SELECT @OldValue = columnname() FROM deleted

SELECT @newValue = columnname() FROM updated

insert into Audit(Old_Value,NewValue ,Update_by,Updated_When,tblName,field_Name)
select @OldValue,@NewValue,user(),current_date(),'mytest',@fieldName from mytest;

Last edited by peter_budo; Dec 12th, 2008 at 7:02 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

Quick Reply to Thread

Show Printable Version

Similar Threads

Other Threads in the MySQL Forum

Previous Thread: Help optimizing a query
Next Thread: MySQL SELECT rows In a Date Range

Thread Tools	Search this Thread
Show Printable Version Email this Page

DaniWeb Message

Cancel Changes