Trigger fails - avoid rollback of insert statement

• jo-608789

  Valued Member

  Points: 65

  More actions

  February 22, 2008 at 11:30 am

  #184234

  Hi,

  if an AFTER trigger on an INSERT statement fails for some reason in SQL Server 2005, the complete INSERT statement is rolled back. Is this right?

  Is there some way to ensure that the INSERT statement is committed and only the statements in the trigger are rolled back?

  Best regards,

  jo

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  February 22, 2008 at 11:55 am

  #781204

  "Is there some way to ensure that the INSERT statement is committed and only the statements in the trigger are rolled back?"

  It depends on the statement in the trigger:

  1. Failure of an insert, update or delete will always rollback everything.

  2. A rollback will rollback everthing

  3. A raiserror will not rollback anything.

  SQL = Scarcely Qualifies as a Language