Create a History table?

Exist a good extension or similar to create a history of changes in all (or most) tables?
i know i can put my code in function beforeSave() in any model, but i need something more reusable and professional.

in my mind i have the structure below.

table history
id, id_user, table_name, change_message, date

1, 25, dbo_address, update(postal_code: old value = 5500, new value: 5501), added(street: Street with no name), 2021-04-04 18:30:00

1 Like

@uldisn thanks!
never heard of audit trail, in my search i found other extension.
what do you think? looks more completed.

yii2-audit is monster.
d3yii2/d3audittrail can easy integrate in model detail view for viewing actual model record history.

1 Like

I think it makes more sense to capture data change history using database trigger code, if you’re using a RDMBS. If you want your app user to see a history of changes, you can always make a view to the history table.

it’s a good idea but in my case i need save id_user from user logged in application.
i thinks it’s no possible from trigger in database. Or is it?

In SQL Server, you can use SYSTEM_USER from the trigger code. I think Oracle has a similar special variable but I forget what it is, if I ever knew. I don’t know about MySql. Of course it kind of depends on how you’re authenticating, so your experience may vary.

@JohnBrooking in my first search i think is complicated in mysql.
In this case yii2-audit (or similar) it will be a better option?

I don’t know, I’m not familiar with yii2-audit. This does seem to be a case of Perl’s motto, “There’s More Than One Way to Do It.” :slightly_smiling_face:

… take a look at e. g. CDC in terms of MySQL, this should be solved in my opinion at database level,

Bg Toby