Oracle has some kind of Fine-grained auditing (FGA), introduced in Oracle9i, allowed recording of these row-level changes, so we know what data has been write to a data cell.
update SCOTT.EMP set salary = 12000 where empno = 123456;
How do you track this activity in the database?
Usually Audit trial lets you know that Joe updated the table EMP owner by SCOTT, but it does not show that he updated the salary column for the table for employee number 123456. It does not show the value of the salary column before the change, either¿ to capture such detailed changes.
In MySQL, we need trigger for all AFTER insert/update and BEFORE delete on a table and write to another table created for this kind of information, then create the respective trigger for that (AFTER insert/update and BEFORE delete)
We have code here that:
1) Create a separate audit table with same structure as source table, with table name as DBAudit_<source table>. e.g. :
CREATE TABLE DBAudit_AB LIKE AB;
2) Add fields on DBAudit_AB:
ALTER TABLE DBAudit_AB
ADD DBAuditID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,
ADD TriggerAction VARCHAR( 10 ) NULL AFTER DBAuditID,
ADD Action CHAR( 10 ) NULL AFTER TriggerAction ,
ADD ActionDate DATETIME NULL AFTER Action ,
ADD ActionBy VARCHAR( 50 ) NULL AFTER ActionDate ;
3) Create trigger:
3) set delimiter to ||
CREATE TRIGGER AB_INSERT_TR AFTER INSERT ON AB
FOR EACH ROW
INSERT INTO DBAudit_AB SET
<field 1>=NEW. <field 1>,
<field 2>=NEW. <field 2>,
<field 3>=NEW. <field 3>;
If the application has 3000 > tables, then we have to turn on trigger for each of them, this make the data audit highly unmanageable.
A method is need for the situation when table is removed/added, it should be about to change the under layer DBAudit table automatically,
If Table A changed fields (add/remove), anyway to know it quickly and execute the whole change in PHP code?
There can be 2 x situtations:
1) Table A has new field added.
2) Table A has field removed.
We want to build a method here that do data level audit automatically, image one table has changed fielded, how can the respective DBAudit_<table> know there are changed of fields and then add/delete the respective new field on the DBAudit_<table> to start data level audit again.?
Anyway in PHP to change the same change to table AB so that A and AB has the same number of field again?