Triggers – Oh What a Feeling
As a follow-up on SQL Trigger discussion I have a few comments and a lengthy reader response.
Before declarative referential integrity was introduced in SQL Server we used triggers (ancient history). I work with customers who have partitioned their data into multiple databases. In those cases, a trigger is the only way to enforce referential integrity across two different databases.
Again, this may be a design flaw. If the data is truly related, then why is it in different databases? Using a trigger tightly binds the databases together by using a heterogeneous query (query involving more than one database). There are ways around this issue beyond the scope of this editorial.
Using a solid data persistence pattern such as a repository pattern will resolve many issues better than a trigger. That is because the repository pattern will expose a bad database design.
However, many times we don’t have control over either the client code or the schema design. So sometimes you will have to fall back on other techniques.
Maurice has some additional thoughts on the matter of Triggers:
Some people use Sql triggers as a way to avoid refactoring their application that have a poor database. It is not a trigger problem, it is a management problem.
You’re right to say that triggers have to be written in the event that multiple rows may appear in Deleted or Inserted and that some junior developers don’t do it. It is a competence problem. For people you are not able to do it, or don’t have time to refactor them, I suggested to add as the first instruction in the trigger : If @@rowcount > 1 raiserror (“Trigger xxxx is not made to handle multi-row insert/update/delete”). The day the application will start to do multi-row modifications in a single statement, this will show up right at development time.
Now, even for those who are aware that triggers should always be coded to handle multi-row modification, they rely on the only one technique they know : Cursors. Most people don’t know some simple techniques to express business logic in set based expressions that cascade modifications to other tables. When set-based expressions are well designed they perform order of magnitude faster than cursor based logic. The simplest one, the most ignored, is using Inserted, or Deleted tables in a join into a data-modification statement. Also use them in a CTE to get the new values that business logic requires and use this CTE into the data modification statement that cascade modification to other tables. The CTE will return to the data modification statement the new values expressed by business logic. This type of code is often shorter, leaner and way off faster. I see often big procedural logic wrapped in endless cursors that could be easily re-written in set-based mode.
Junior T-SQL programmer should not write triggers not being reviewed by someone experienced in writing T-SQL code.
Last point, moving business logic somewhere else doesn’t solve the basic problem that sometimes requires trigger: data design. More and more people are confused about this subject. They think that this is a relational database problem, while this is a normalization problem. People think data normalization is only for relational database design, and I think it is wrong. No matter the technology, data-redundancy will induce more and more work for developers. Data attributes incorrectly associated in a single unit of data (let’s say a record or a row) induce logic problem.
Triggers are often used as a means to address poor database design. Poor database design is evil not triggers.
Thanks for the input. See you all next week.
Cheers,
Ben