tracing value changes in my tables

[Sally123]

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

[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 ;)

[Sally123]

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 .

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;