Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elentz
Starting Member
2 Posts |
 Posted - 2009-05-07 : 07:41:41
|
| We are performing a data migration and have many triggers that update a control table to tell us what needs migrated. What can cause a trigger not to fire?A couple examples I'm thinking of:- What happens if a tablespace becomes full?- What if the trigger refers to a dependent object in the database and something goes wrong with that dependent object?What else could go wrong? Can a trigger be disabled based upon some event in the database? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-07 : 07:49:22
|
| Is this loading into a SQL Server database? Tablespaces are not used in SQL Server, the nearest analog is a filegroup.If a database file fills up and can't expand, it could abort the operation, but that could happen while the trigger is running. A missing dependent object wouldn't prevent the trigger from firing, it would likely throw an error.Triggers can be disabled in SQL Server using the ALTER TABLE command. There are some bulk insert operations that can bypass triggers. We'll need more details of what you're trying to do to provide more assistance. |
 |
|
|
elentz
Starting Member
2 Posts |
Posted - 2009-05-07 : 09:48:39
|
| You caught me... it is for SQL Server, but my background is Oracle, hence the incorrect terminology. With Oracle if a dependent object goes offline, so would the stored procedure (trigger), which would sometimes not come back online (at least with older versions) even after you corrected the object which caused the problem.Maybe my "for instance" is confusing things? My main question lies around some, even not common, cause which would result in a trigger not triggering in SQL Server - aside from bulk loads (but thanks for noting that). |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-07 : 10:23:02
|
Only other thing I can think of is a transaction that gets rolled back. From Books Online:quote:
Rollbacks and Commits in Stored Procedures and TriggersIn TriggersA trigger operates as if there were an outstanding transaction in effect when the trigger is executed. This is true whether the statement firing the trigger is in an implicit or explicit transaction.When a statement begins executing in autocommit mode, there is an implied BEGIN TRANSACTION to allow the recovery of all modifications generated by the statement if it encounters an error. This implied transaction has no effect on the other statements in the batch because it is either committed or rolled back when the statement completes. This implied transaction is still in effect, however, when a trigger is called.When a trigger executes, an implicit transaction is started. If a trigger completes execution with an @@TRANCOUNT = 0, an error 3609 occurs and the batch is terminated. For this reason, inside of triggers it is advisable to avoid using ROLLBACK TRANSACTION, which resets @@TRANCOUNT to 0, and COMMIT TRANSACTION, which may decrement @@TRANCOUNT to 0. Issuing a BEGIN TRANSACTION statement after a rollback will stop the error from being raised, but this could cause problems with application logic. It is important to understand that a BEGIN TRANSACTION statement issued in the trigger is actually beginning a nested transaction. In this situation, executing a COMMIT TRANSACTION statement will apply only to the nested transaction. Because a nested BEGIN TRANSACTION statement is ignored when rolling back nested transactions, ROLLBACK TRANSACTION executed in the trigger rolls back past any BEGIN TRANSACTION statements issued by the trigger itself. ROLLBACK rolls back to the outermost transaction and sets @@TRANCOUNT to 0.When using ROLLBACK TRANSACTION in a trigger, be aware of the following behavior:All data modifications made to that point in the current transaction are rolled back, including any that were made by the trigger.The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back.A ROLLBACK in a trigger closes and deallocates all cursors that were declared and opened in the batch containing the statement that fired the trigger. This includes cursors declared and opened in stored procedures called by the batch that fired the trigger. Cursors declared in a batch prior to the batch that fired the trigger are only closed. However, STATIC or INSENSITIVE cursors are left open if: CURSOR_CLOSE_ON_COMMIT is set OFF.The static cursor is either synchronous or a fully populated asynchronous cursor.Instead of using ROLLBACK TRANSACTION, the SAVE TRANSACTION statement can be used to execute a partial rollback in a trigger.
I'd suggest auditing your migration process for any of the conditions mentioned above, and also for any implicit or explicit rollbacks or error conditions that could cause a rollback. |
|
|
|
|
|
|
|
|
Any way for a trigger not to fire?