3

I'm using a InnoDB engine in MySql and I'm trying to figure out if it is possible to know the datetime of the last time a table was modified (be it it's data or it's structure).

Since it's InnoDB, I cannot use the column update_time in information_schema.Tables as nothing is logged there. The performance of information_schema with a lot of data is crappy anyway, so no big lost.

I thought about using a trigger that would insert the date in a self-made "metadata" table when a modification is made, but MySql doesn't support DDL triggers (Create, Alter and Drop statements), so this won't work either.

I'm kinda running out of ideas here, any tips?

Thanks

Edit: I forgot to specify that this datetime data will be retrieved (via a web service) by a Silverlight application I'm developing.

To give more background: I have a method on my web service that returns the structure of a database, with its data. Depending on the size, this could be a relatively long operation since I must get foreign key information in the information_schema table. So I want to query the database only if the data or structure as changed.

AdamL
  • 12,421
  • 5
  • 50
  • 74
Mathieu
  • 1,638
  • 2
  • 27
  • 50

1 Answers1

0

Ok you can use audit trail by creating a trigger for a table

example

when inserting recored

CREATE TRIGGER emp_insert AFTER Insert ON hs_hr_employee FOR EACH ROW BEGIN
INSERT INTO hs_hr_audit (audit_table_name, audit_row_pk, audit_field_name, audit_old_value, audit_new_value,audit_datetime,audit_user,audit_description) VALUES ( "hs_hr_employee", NEW.emp_number, null, null, null,NOW(),@user,"new record added");
END;

for update tabel

CREATE TRIGGER emp_update AFTER UPDATE ON hs_hr_employee FOR EACH ROW BEGIN IF NOT( OLD.emp_number <=> NEW.emp_number) THEN INSERT INTO hs_hr_audit (audit_table_name, audit_row_pk, audit_field_name, audit_old_value, audit_new_value,audit_datetime,audit_user,audit_description) VALUES ( "hs_hr_employee", OLD.emp_number, "emp_number", OLD.emp_number, NEW.emp_number,NOW(),@user,"record updated"); END IF;
IF NOT( OLD.employee_id <=> NEW.employee_id) THEN INSERT INTO hs_hr_audit (audit_table_name, audit_row_pk, audit_field_name, audit_old_value, audit_new_value,audit_datetime,audit_user,audit_description) VALUES ( "hs_hr_employee", OLD.emp_number, "employee_id", OLD.employee_id, NEW.employee_id,NOW(),@user,"record updated"); END IF;
IF NOT( OLD.emp_lastname<=> NEW.emp_lastname) THEN INSERT INTO hs_hr_audit (audit_table_name, audit_row_pk, audit_field_name, audit_old_value, audit_new_value,audit_datetime,audit_user,audit_description) VALUES ( "hs_hr_employee", OLD.emp_number, "emp_lastname", OLD.emp_lastname, NEW.emp_lastname,NOW(),@user,"record updated"); END IF;
IF NOT( OLD.emp_firstname <=> NEW.emp_firstname) THEN INSERT INTO hs_hr_audit (audit_table_name, audit_row_pk, audit_field_name, audit_old_value, audit_new_value,audit_datetime,audit_user,audit_description) VALUES ( "hs_hr_employee", OLD.emp_number, "emp_firstname", OLD.emp_firstname, NEW.emp_firstname,NOW(),@user,"record updated"); END IF;

do some search on audit trail on google for more details

Roshan Wijesena
  • 3,106
  • 8
  • 38
  • 57