Is it recommended to use trigger to manage business rules with SQL Server?
-
What is the cost of a trigger in SQL Server? Could we use them or should we avoid to use them? What are the best practice about triggers? Can we use them as business rules? I'm trying to check the status of a record. I have a table with job offers. Each job offer can have one or more application with differents status. For simplicity let says step 1, 2, 3, 4 and 5. When an application reach the step 5 I must check I have only one application with status 5 by job offer and I must set all other application to step 4. Am I clear? Can I do that or this kind of complex check with a trigger?
-
What is the cost of a trigger in SQL Server? Could we use them or should we avoid to use them? What are the best practice about triggers? Can we use them as business rules? I'm trying to check the status of a record. I have a table with job offers. Each job offer can have one or more application with differents status. For simplicity let says step 1, 2, 3, 4 and 5. When an application reach the step 5 I must check I have only one application with status 5 by job offer and I must set all other application to step 4. Am I clear? Can I do that or this kind of complex check with a trigger?
SQL tables and their relationships/indexes/keys are part of the business logic - so you definitely have business rules on SQL. But, there is a certain level of what to put on SQL and what to save for your code... Triggers are very powerful to do other things while something happened to one of your tables, but it has a performance penalty. I saw databases where an insert that should take of a fraction of a millisecond took over 2 seconds because of the complex computations and data changes from within a trigger. Also if you got into the trigger things you can easily get a situation where trigger fires trigger that fires trigger...and so on. A very unpleasant situation to deal with when errors occur... So do not use triggers if you can avoid, try to create stored procedures for complex insert/update scenarios!
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
-
What is the cost of a trigger in SQL Server? Could we use them or should we avoid to use them? What are the best practice about triggers? Can we use them as business rules? I'm trying to check the status of a record. I have a table with job offers. Each job offer can have one or more application with differents status. For simplicity let says step 1, 2, 3, 4 and 5. When an application reach the step 5 I must check I have only one application with status 5 by job offer and I must set all other application to step 4. Am I clear? Can I do that or this kind of complex check with a trigger?
I second Kornfelds opinion that you should avoid triggers. •The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back.[^]. They are hell to debug.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
SQL tables and their relationships/indexes/keys are part of the business logic - so you definitely have business rules on SQL. But, there is a certain level of what to put on SQL and what to save for your code... Triggers are very powerful to do other things while something happened to one of your tables, but it has a performance penalty. I saw databases where an insert that should take of a fraction of a millisecond took over 2 seconds because of the complex computations and data changes from within a trigger. Also if you got into the trigger things you can easily get a situation where trigger fires trigger that fires trigger...and so on. A very unpleasant situation to deal with when errors occur... So do not use triggers if you can avoid, try to create stored procedures for complex insert/update scenarios!
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
Kornfeld Eliyahu Peter wrote:
trigger fires trigger that fires trigger...and so on.
With SQL Server there is an inbuilt protection system where you will not get a recursive firing of triggers. I tested this recently as I was concerned about this sort of behaviour.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Kornfeld Eliyahu Peter wrote:
trigger fires trigger that fires trigger...and so on.
With SQL Server there is an inbuilt protection system where you will not get a recursive firing of triggers. I tested this recently as I was concerned about this sort of behaviour.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
You are talking about recursive triggers. I'm about nested triggers... ;)
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
-
You are talking about recursive triggers. I'm about nested triggers... ;)
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
Does it protect against recursion within your nesting?
Wrong is evil and must be defeated. - Jeff Ello[^]
-
Does it protect against recursion within your nesting?
Wrong is evil and must be defeated. - Jeff Ello[^]
No.
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
-
No.
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
Bugger. :) I'll keep staying clear from triggers then.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
Bugger. :) I'll keep staying clear from triggers then.
Wrong is evil and must be defeated. - Jeff Ello[^]
As far as you can!!!
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
-
What is the cost of a trigger in SQL Server? Could we use them or should we avoid to use them? What are the best practice about triggers? Can we use them as business rules? I'm trying to check the status of a record. I have a table with job offers. Each job offer can have one or more application with differents status. For simplicity let says step 1, 2, 3, 4 and 5. When an application reach the step 5 I must check I have only one application with status 5 by job offer and I must set all other application to step 4. Am I clear? Can I do that or this kind of complex check with a trigger?
I'm with the other 2, their arguments are perfectly valid, and here is another, triggers spit are a support nightmare, digging out an error in a nested trigger can drive you nuts. Triggers spit have their place, they are excellent for auditing and logging changes to your data ONLY.
Never underestimate the power of human stupidity RAH
