When Does It Make Sense to Use Database Triggers for Data Normalization?

You could do it that way.

A more contemporary way would be to do it in non-DB business logic at the point of entry (e.g. a web service that accepts or otherwise processes the raw ads).

This way you can easily add complex logic, logging, feedback etc. Database logic can struggle with some of this stuff, but has the benefit of being able to be applied as bulk updates.

Combining the two, you could have DB constraints to check the final result, e.g. name does not have line breaks.

Horses for courses - depends on your architecture and need for bulk, but I'd do something like what I outline above - web service to accept data, DB constraints to ensure rules are applied.

Database normalization is focused on reducing or eliminating anomalies when inserting, updating, or deleting data; and ensuring flexibility for future enhancements to the data schema.

Checking if an ad has line breaks in it is, in my opinion, not a part of normalization; it's a business rule, not a data constraint.

My answer is going to end up being like LoztInSpace's answer; enforce this as a data validation in the application tier of your architecture. I'm going to use the term "application tier" very loosely, though. I've worked on applications where the database was just storage, and other applications where all data modifications were achieved using stored procedures making the database its own API.

You'll need to decide for yourself where this best fits within the architecture you currently have. If these business constraints are being enforced in the database as a matter of convention, then consider using a stored procedure instead.

You cannot bypass a trigger, but you can bypass a stored procedure with direct inserts, updates, or deletes to a table. This flexibility is convenient when importing data from an outside source. You can always revoke insert, update and delete permissions from the user that connects to the database to perform data manipulation statements, giving them only execute permissions for the stored procs.

My preference is to handle this check in a more expressive language that has a more intuitive toolset for writing tests. This usually means one of the major programming languages, e.g., C#, Java, Python, JavaScript/TypeScript, PHP, Ruby, Go, etc. Testability is the bigger win here.

I don't think it makes sense to use a trigger for data normalisation. (maybe more accurate to call this "data cleansing")

There are a couple of reasons,

1. As @LoztinSpace points out, a modern approach would be to use a coded API layer to perform this kind of logic and only to use the database for persistence.

   This is prefered for two main reasons,

   a. The DB's own API layer, views, sprocs, triggers etc is more constrained than a generic programming language. Where you could for example call an external API as part of the process and

   b. Because a programmed API can be scaled more easily than a database.

2. You would expect the data to be "normalised", before it was stored.

   Given that triggers normally operate on a DB level change it will be tricky to get them to normalise data before its inserted. Now there is Instead of Insert and you could have a preload temp table etc, but I think the concept of DB triggers doesn't fit well with the idea of data "normalisation".

   If you did want to avoid a separate programmed API layer, you could use a SPROC or even better an ETL job to ingest the data.

So even if we exclude the idea of a separate API layer for some reason, I still think using a database trigger to achieve this process would not be a good choice.

I don't think it ever makes sense to use triggers for what you are describing.

The basic problem with triggers is that they add an undesirable (and almost always unexpected) "reactivity" to database engines. They are also tricky to get right - tricky to the degree of often tripping up experts, and novices never getting them right.

If you wanted some automatic enforcement at the database layer, then a CHECK constraint to reject unclean data would be your best bet.

If you want some logic to actually perform the cleansing and modify data, this should either be done in a stored procedure prior to the data being committed, or outside the database prior to submitting.

In my experience, triggers have very little gainful employment within a database.

If possible, the absence of a trigger should be preferred over the presence of a trigger. A read-only trigger should be preferred over a write trigger. And a write trigger on a base table, should never change inserted data in-flight (i.e. it should only modify auxiliary fields which are not the focus of the DML statement, or create additional rows). A write trigger on an updateable view should only change inserted data in-flight, if it is a reversible change which is undone when the data is queried back from the view.