Hello everyone,
i want to track all my model changes (create, update, delete). If i use for example the package of spatie laravel-activitylog, i get the activities listed or not? Do I still need the columns (created_at, updates_at) in my original tables? Am I perhaps not paying attention to something?
Is it a disadvantage to list all changes in one change table? I think about the fact that the amount of data becomes very large at some point. Can someone tell me how to customize a package so that I have a change table for each model?
Created_at and updated_at are optional and can be left out if you wish.
When it comes to logging data one table should be enough. Keep in mind that you will need to remove old data at some point. Either delete it or move it to another table.
I would not handle the logging in Laravel at all. I would use database triggers for this.
Thanks for your reply.
Why would you use triggers? What are the benefits compared to laravel?
It's faster and that is basically what they are for. You also only hit the database once and not twice as it would when doing it on the php side.
Is it possible to trigger only the changed fields? And does it make more sense to trigger each column separately or is it better to combine everything in one column (type string) in the "history-table"?
Can you give me an example of how you solved it?
Yes it is possible and it's the right way to do it.
Here is an example
I have read the article. However, I do not want to trigger each changed fied as a separate entry/row in the database. I want a string with All entries of the changes similar to an array or in json Format, e.g.:[field1{oldf1,newf1}, field2{oldf2, newf2},...] Or is that not a good idea?
That would work however it would be clunky to read.
I would do field by field. And the table would look something like this
Name Null? Type
----------- -------- -------------
TABLE_NAME NOT NULL VARCHAR2(30)
FIELD NOT NULL VARCHAR2(50)
TIMESTAMP NOT NULL DATE
COLUMN_NAME NOT NULL VARCHAR2(30)
FROM_VALUE VARCHAR2(100)
TO_VALUE VARCHAR2(100)
USER NOT NULL VARCHAR2(30)
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
So a record could look like this
table | field | from_value | to_value | user |module | action |
--------------------------------------------------------------------------------------------
users | email | [email protected] | [email protected] | admin | user update | update |
users | name | null | Karen | admin | user create | insert |
Thank you for your example. And how can I then design a database query so that I can capture all entries for a change? This way I can better present the whole thing in table form. E.g.if two entries have been changed, I have two new rows in the histoy-table. But these belong to one change command of the user.
Do I need to add an additional column with the version, so that both columns would have the same value in that column? Or is there a more elegant way?
You can alway store a timestamp in a variable and then insert it into the log table and all changes that has the same timestamp are done at the same time.
Thanks. I will try it.
And when two users update the same table at the same time but different entries, i must query the results, where the timestamps and users are equal, right?
Something like that yes. In my experience you just need to know if a field has been updated, how many times , when and by whom.
Please or to participate in this conversation.