 |
|
|
Home > Why use triggers in Microsoft SQL Server? |
|
|
|
|
|
|
Why use triggers in Microsoft SQL Server? |
|
| 02 May 2006 | SearchSQLServer.com |
|
|
Intro | Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7
SQL Server Books Online states that "triggers are a special class of stored procedure defined to execute automatically when an UPDATE, INSERT or DELETE statement is issued against a table or view."
The problem with triggers is that they cannot be "seen." It is easy to view table relationships, constraints and indexes in the database. On the client-application side, it is also simple to analyze the code. On the other hand, triggers are automatic programs defined in the database that execute behind the scenes as part of the command that fired them. It is hard to follow their logic and in time, it is easy to forget about the triggers in SQL Server, especially if they are not well-documented.
Here are some trigger design tips to keep in mind:
- Avoid using nested triggers
By default, if a trigger is changing other tables, the triggers declared for these tables are not fired. The "allow nested triggers" server option sets databases to have the opposite behavior. Triggers are nested when a trigger performs an action that initiates another trigger, which can initiate another trigger and so on. Triggers can be nested up to 32 levels. It is very difficult to follow the logic of nested triggers and they can affect performance.
- Avoid using recursive triggers
There are two types of recursion:
- Direct recursion occurs when a trigger fires and performs an action that causes the same trigger to fire again. You can prevent that from happening by setting the "recursive trigger" database option to OFF.
- Indirect recursion occurs when a trigger fires and performs an action that causes a trigger on another table to fire. This second trigger causes an update to occur on the original table, which causes the original trigger to fire again. This can be prevented with the "nested triggers" server option.
Note that INSTEAD OF triggers commonly have to perform at least one SQL command (UPDATE/INSERT/DELETE) as they are executed, instead of the command that fired them. You have to design them carefully keeping in mind upcoming schema changes. Sometimes you simply have to hard code the field list and remember to change it in the future as needed. As an example, if you have to insert a row into a table with an identity column, you can't just insert into <table> select * from <another table>.
In this case, you have to state all the required fields, except for the one that holds the identity values.
Explore Microsoft SQL Server triggers
 Home Introduction
Part 1 Why use triggers in SQL Server?
Part 2 Design and implement triggers in SQL Server
Part 3 Example: INSTEAD OF triggers vs. AFTER triggers
Part 4 Example: One trigger vs. two for the same logic
Part 5 Example: Nested triggers in SQL Server
Part 6 Example: Heavy insertion process from OLTP and triggers
Part 7 Example: Cursors in SQL Server triggers
| ABOUT THE AUTHOR: |
|
Michelle Gutzait works as a team member of the SQL Server Database Experts at www.pythian.com, a worldwide company providing Remote Administration services for databases, operating systems, SANs and networks. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the past 10 years, she has worked exclusively with SQL Server. Her skills include SQL infrastructure and database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS/SSIS packages, administrative and infrastructure tools development, reporting services and more. |
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
| |
