Triggers are stored batches of T-SQL, much like stored procedures. But instead of being called directly, they execute indirectly when you execute an INSERT, UPDATE, or DELETE operation. You can do almost anything in them that you can do in T-SQL, with only a few caveats. In capable hands-particularly the hands of those who understand the caveats of triggers-they can fill gaps left by constraints by allowing you to handle problems that constraints are incapable of and have the solution automatically occur without user or programmer involvement. On the other hand, in the wrong hands, they can drag down the performance of your system faster than you can kiss a duck. Some people feel that triggers are more evil than cursors (and putting a cursor in a trigger is a capital offense in some locales). Others use them for just about everything, often to do the sorts of simple checks that you could use a CHECK constraint for. The truth is somewhere in between.
There's an overhead in using the inserted and updated virtual tables to access the modified rows inside the trigger, and this overhead needs to be considered before using triggers. There are two different types of triggers. First there are AFTER triggers, which execute after the rows are in the table and after constraints have been applied. These triggers are most often used to validate some condition or to cause some cascading operation that can't be done declaratively using a CHECK constraint.
The second type is called INSTEAD OF triggers, and they fire as the first operation before any other actions, even before the row is in the table. Instead of a trigger, you'll have to manually execute the operation you want to occur. For example, if you have an INSTEAD OF INSERT trigger, you have to code the INSERT statement in the trigger to do the INSERT that the trigger fired for. INSTEAD OF triggers can be useful for formatting data, or possibly redirecting data to a different table. An easy example is directing data that's unusual to a holding table, such as taking all data from a data entry clerk but putting out-of-normal-range data into a table for more validation. In this scenario, the user doesn't know there was anything wrong, his job is just to key in data (and yes, no doubt a slap on the wrist will come to users who enter too much out-of-range data, but we can't govern the social aspects of our software, now can we?). INSTEAD OF triggers can also be placed on views as well as tables to allow DML on a view to modify more than one table at a time. Triggers are useful as a last resort when you can't use one of the previously mentioned tools to do the same thing. They have the benefit that you can't "forget" to invoke them, particularly if you don't have coding practices that require all modifications to an object to use a common object that knows how to protect. The following list represents the main types of uses I have for triggers, and even this list is apt to start a minor argument between T-SQL experts:
Cross-database referential integrity (RI)-SQL Server doesn't allow declarative constraints across database boundaries, so triggers can be written to validate that data exists in a different database. Databases are independent containers, so this method isn't nearly as good as having constraints in the same database, but as long as you understand that you have to be careful with cross-database references, it's definitely usable.
Intra-table, inter-row constraints-These are used when you need to see that the sum of a column value over multiple rows is less than some value. You can use a constraint with a function, but the query will be executed once per row modified, whereas a trigger need only run the query a single time.
Inter-table constraints-When a value in one table relies on the value in another, triggers are useful for checking for correct values. This might also be written as a functions-based CHECK constraint, but it's often more maintainable to use a trigger.
Introducing desired side effects to your queries-Constraints support cascading operations on UPDATE, but it's possible to come up with many different side effects that might be desired. For example, cascading inserts, maintaining denormalized data, logging who modified a row, and so on.
Triggers come at a price, and the biggest concern is performance. Triggers fire once per DML operation, so whether you modify 1 or 1,000 rows, the trigger fires only once. During trigger execution, SQL Server sets up two tables, one called inserted, which contains all rows that are to be created (or are to be used instead of triggers), and another for removed rows called deleted. You have to be very careful that your code considers the number of rows involved in the operation and the trigger can handle multiple rows being modified. The inserted and deleted tables aren't indexed, and are reflections of the changes that have been made to the table and are captured in tempdb. Hence, you need to be careful with the queries in the trigger to make sure that you're ready for 1 or 1,000 rows to be updated. In some cases, you won't be able to optimize a trigger for large numbers of rows, so you might have to set a cutoff point and fail the operation for large numbers of changed rows. Each of the values such as <name> is something to replace, and there are three places where code is placed. The validation is where to put code that's used to validate the data that has been updated. I rarely use this in an INSTEAD OF trigger because any validation you can do in an INSTEAD OF trigger you should do in a constraint. The modification is where to update data in this or another table, or for an INSTEAD OF trigger, to perform the action that the trigger fired for.
And the log errors is used to log errors after the transaction has been rolled back. The query is built in three layers, the first of which is almost always a join or a correlated subquery to the inserted or deleted table to get the rows to check. For the INSERT and DELETE triggers you'd need to complete the solution, you could vary the subquery to reference only the inserted or deleted tables respectively. In this particular example, you could use the same trigger, but I usually try to have a trigger per action, which makes it easier to add additional code and easier to test. Admittedly, I use tools to create triggers that allow parts of the code to be built in a macro, which allows coding modularity while producing specific code. I do this because in most cases it's better for T-SQL code to be as specific as possible, even if that means writing hundreds of similar procedures or triggers. No, it doesn't seem like a good idea for code reuse, but it's much better for performance. If you want to stop the trigger because of invalid conditions, you raise an error and the TRY...CATCH block handles the rest.
If you use the trigger to make modifications to other tables, you don't really need to do anything else; the error handler will capture any errors that occur, meaning that you don't have to check the error level after each statement. If an error occurs, it'll take you to the CATCH block immediately.
Our website is not responsible for the information contained by this article. Articleinput.com is a free articles resource thus practically any visitor can submit an article. However if you notice any copyrighted material, please contact us and we will remove the article(s) in discussion right away.
Note: This article was sent to us by: Kenneth W. Turner at 01072010
1. What can be used as a key in Python
All articles are property of their respective authors. Please read our Privacy Policy!
© 2009 ArticleInput.com.