Trigger As Sheriff at an Unruly IT Shop?

Question

Erland: "...my strong recommendation is to keep the trigger. This is exactly the type of code you should have in a trigger. If you rely on a stored procedure, and someone runs an update outside the stored procedure, you stock numbers will be incorrect."

The official line is to use triggers when nothing else (stored procedure, view, constraint, etc.) will do or for some reason the trigger solution is superior. Typical use for triggers: business rules enforcement across the enterprise.

I fully understand what Erland is saying, yet, the proper solution would be to end the unruly environment at the IT department, introduce some discipline with operating procedures. 

I can see though, that it is easier to write a trigger than change an IT shop "culture".

Triggers are misused many ways, so what if we add one more?

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Windows Azure SQL Database Programming & Design

Answer 1

I don't see why triggers would be in conflict be with discipline with operating procedures. Rather, I would say triggers can be a way to enforce that.

Triggers are not always suitable. Particularly, if you cannot accept that your statement fails, if you trigger fails, trigger was not the method you should have chosen. Nor is a trigger the right choice, if you don't need an synchronous update, particularly not if you are updating a remote data source.

But if you need a cascading update in the same database, a trigger is often the best choice. Putting the cascading update in a stored procedure incurs the risk that the procedure may be bypassed by a direct INSERT/UPDATE/DELETE statement.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

> incurs the risk that the procedure may be bypassed by a direct INSERT/UPDATE/DELETE statement.

Adhoc updates to the database would not happen at an IT shop with discipline. Everybody is supposed to use the tested/QAd/deployed stored procedures. If the DBA has to do some emergency adhoc update, there is an approved, published procedure for that also.

I can see your point though, there are plenty of undisciplined IT shops where a good guy would implement sheriff triggers to stabilize the system.

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Windows Azure SQL Database Programming & Design

Answer 3

I don't write my code under any assumption about "discipline". I've been in this business long enough to know that if it can happen, it will happen.

---

Erland Sommarskog, SQL Server MVP, [e-mail]