Good practices for storing logs

Trigger there is an extra consumption of SQL, because it will be monitoring everything, then an overload in all processes. I do not recommend. Of course, developing external software is better, but as long as you can develop, know a language, etc. But if you can, opt for external software instead of Trigger. NOTE: Trigger and Cursor in MSSQL are 2 things that consume many features, avoid using them always.

About what to store? I do not understand your question, it depends on you, what do you think is important? Is it from some software we're talking about? You should remember some factors:

• Is this information important? If so, how should I store it? (varchar, int, etc.)
• How long should I store the information? If the information has a shelf life, that is, after X time will not serve anything else, then create a purge tool to remove old information.
• What is the size of this table, each field has its consumption, eg int = 8bytes, varchar consumes in bytes the size itself, nvarchar to an overload of + 1bytes per field., added all columns, multiply by the amount of records, so you can know what size, 20mb, 200mb, 2gb per day, week, month.
• Another tip, you have to think better, if it is just a log, I imagine it is an audit of everything the user is doing, so avoid creating indexes in this table, or think well before creating, every time you has an index there is a consumption to insert records.

If you want to specify better, show an example of the table.

Good logging practices follow two basic principles:

Efficiency of a log

The log should contain verbose values, for example key = value (name="john doe"), so that it can be read by a human. Logging of binary information should be avoided.

The timestamp must always be registered for all events. The correct date / time is essential to see if something has gone wrong.

You should use Unique ID's in the various events / transactions. The use of UID, and its logging, uniquely identifies a record. It is also very useful in debugging processes.

As a basic principle, the following information should be stored:

• Who (who)
• What (what)
• When (when)
• Where (where)

Log Security

A log to be safe, must be secured ideally at the database level. When logging is ensured programmatically, there is the possibility that the programmer forgets to log a particular event. If logging is assured at the database level, logging of all events is natively ensured.

A log should follow the WORM (Write Once Read Many) principle.

A log should have a checksum / hash so that its consistency can be evaluated.

Ideally, the log should be saved off-site (on another machine) so that it can be compared retrospectively with the "original" log.

Disclamer : The good practices I've outlined here are suitable for critical business applications.

just my 2 cents

All the ways are correct, however as the application grows your bank will inflate and your audit tables will get gigantic and slow.

In several applications that I worked with that audited, all ended up having this same problem. At first it seems like a good idea but in the long run it ends up becoming a bad headache.

In new applications I try to abstract this problem (not only this type of problem, but also others whose application has no responsibility to handle) using a Saas. This is a great solution because you will let a specialized audit firm solve your problem. Simply you will have to consume an API that they will make available to you, and they will solve your problem. In addition, all audit data will be left out of your database because they will be within this service and will be their responsibility to solve problems of slowness etc, and you will focus on what is important, your business rule.

I recommend you use AuditingShip , Saas Audit. Some companies are already using it and are enjoying it a lot. Some advantages:

• It's quick to make a "Hello Word" question of 5 minutes or less;
• You can use any language that consumes REST, it even shows some examples with JavaScript and Ruby;
• Simple and dynamic documentation.

It depends a little on the technology you are using but the war is right.

By trigger it makes maintenance difficult and you will have to implement the trigger in n tables. It is not practical if you have many tables and in general triggers do not facilitate maintenance and debugging. On the other hand, it is a quick and handy method if you have few tables being audited, for example if you are only auditing a table of logins. Another advantage is not having to tinker with legacy or third-party codes.

By code would be the "most correct" from the point of view of OO when you are creating a system. You can simply create an Auditable class for example and inherit your DB classes from this class, automating the audit process, reusing code, and centralizing maintenance of this functionality in that class. You can use extension methods or anything of the same kind.

If you use .Net you can use Log4Nnet for example, which is a framework that already automates a lot of the work of creating logs of your applications. Try to see if the technology you use no longer has a similar tool.