0

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

2
Contributors
2
Replies
3
Views
8 Years
Discussion Span
Last Post by Sally123
0

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

0

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;
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.