Saving each updated field in the history table with user id

Applications Forms
#1

Hi guys;

I’m new with SC, now my client wants me to add “History” link on each field, when user click on “History” link the it should show the history with user id and field data was previously entered.

Now my problem is how I set-up data on every field updated and save it to the history file which has user id, field data, date and time.

remember the data will save only updated field not all the field when click on update button.
thanks in advance…
Ahmer

#2

Re: Saving each updated field in the history table with user id

Use a trigger in the DB. Sc has nothing built-in. IF you need to perform this in the client end instead, you would have to create your own array function and compare the values.

I use a trigger for all events (A/E/D) and allow the user to review all history to the record.

Regards,
Scott.

#3

Re: Saving each updated field in the history table with user id

Thanks Mr. Martin.

As I mentioned I’m new with SC, to get help can you explain little more to get start, or i can get the sample code and where to put in SC after alter the script.

thanks in advance.
ahmer

#4

Re: Saving each updated field in the history table with user id

sound looks I didn’t mention my problems in detail :frowning:

#5

Re: Saving each updated field in the history table with user id

As mentioned, I would suggest using triggers. You could setup a trigger as follows:
Please note that you will have to change the table/fields to match your schema


Delimiter ;
Delimiter $$
DROP TRIGGER IF EXISTS insert_employee_dates $$

CREATE TRIGGER insert_employee_dates BEFORE INSERT ON employees
FOR EACH ROW 
 SET NEW.date_401k_notify = DATE_FORMAT(NEW.date_401k_eligible ,'%Y-%m-%d'),
  NEW.date_401k_notify = DATE_SUB(NEW.date_401k_notify,INTERVAL 1 MONTH),
  NEW.date_updated = CURDATE()
$$
Delimiter ;

--

Delimiter ;
Delimiter $$
DROP TRIGGER IF EXISTS update_employee_dates $$
CREATE TRIGGER update_employee_dates BEFORE UPDATE ON employees
FOR EACH ROW 
 SET NEW.date_401k_notify = DATE_FORMAT(NEW.date_401k_eligible ,'%Y-%m-%d'),
  NEW.date_401k_notify = DATE_SUB(NEW.date_401k_notify,INTERVAL 1 MONTH),
  NEW.date_updated = CURDATE()
$$
Delimiter ;

--

Delimiter ;
Delimiter $$
DROP TRIGGER IF EXISTS deleted_employee$$
CREATE TRIGGER deleted_employee BEFORE DELETE ON employees
FOR EACH ROW 
 INSERT INTO employee_deleted SELECT * FROM employees WHERE id_employee = OLD.id_employee
$$
Delimiter ;

Regards,
Scott.

#6

Re: Saving each updated field in the history table with user id

very nice Mr. Martin,

would you please tell me where should I put this code, according to table fields… also i’ve 55 fields in the table, shell I create trigger for each field to run the trigger as soon as field data is changed…

thanks

#7

Re: Saving each updated field in the history table with user id

This code belongs on your DB. Issue it like you would any other SQL statement. It does not belong in your SC project code.

http://dev.mysql.com/doc/refman/5.0/en/triggers.html
http://net.tutsplus.com/tutorials/databases/introduction-to-mysql-triggers/

Regards,
Scott.