quote:The article on Triggers did not mention that the Trigger is fired only one timer per DML statement - Insert, Update, or Delete. The SQL Data Manipulation Statement may affect more than one row and this was not mentioned. An example, or at least a warning should be included. The Delete trigger example showing a rollback tran if the proffessor had tenure would not work if we are deleting more than one row, and the last row processed by the trigger did not have tenure.
quote:There needs to be an example of handling Identity columns and Insert triggers. Triggers sometimes mess up the value returned by @@Identity.
quote:There should be an example of a trigger to track all changes to a database table. It was mentioned in the article, but there was no example.
quote:A trigger always operates as if there were an outstanding transaction in effect when the trigger is executed. This is definitely true if the statement firing the trigger is in an implicit or explicit transaction. It is also true in autocommit mode. 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.This means that any time a BEGIN TRANSACTION statement is issued in the trigger, it is actually beginning a nested transaction. Because a nested BEGIN TRANSACTION statement is ignored when rolling back nested transactions, ROLLBACK TRANSACTION issued in the trigger always rolls back past any BEGIN TRANSACTION statements issued by the trigger itself. ROLLBACK rolls back to the outermost BEGIN TRANSACTION.