Member Avatar for fx.eko

When I try to migrate from SQL Server 2000 to SQL Server 2008 R2 Express, when I run the application program there is an error message transaction aborted by the trigger, whereas before when I was using SQL Server 2000 does not exist error.

after there was a note on the trigger statement to update data that is not given block BEGIN TRANSACTION in SQL Server 2000 while it had no effect.

Does the complexity of SQL server 2008 which made ​​it should not be violated?
It points what should be considered when migrating from SQL server 2000 to sql server 2008??

thanks for the enlightenment

  • 2 Contributors
  • 1 Reply
  • 319 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by Member #949455

Dani AI

Generated

reported that migrating the DB to SQL Server 2008 R2 produced a “transaction aborted by the trigger” error while the same workload ran on SQL Server 2000. That message most often means the trigger itself ended the transaction (explicit ROLLBACK or a fatal error inside the trigger). Triggers execute inside the same transaction as the firing statement, and if the trigger ends the transaction the batch is aborted (SQL Server returns Msg 3609). This behavior is the engine’s transaction model for triggers, not a mysterious new “complexity” in 2008 R2. (sommarskog.se)

Practical diagnostic steps (repeatable, safe on a dev copy): run the failing statement by hand in SSMS and capture the full error text and message number; enable an error trace (Profiler or Extended Events) to see any earlier error that caused the trigger to roll back; enumerate DML and DDL triggers and inspect their code for ROLLBACK, BEGIN TRAN, RAISERROR/THROW or unguarded operations. Example queries to find trigger code quickly:

SELECT tr.name, tr.parent_class_desc, OBJECT_NAME(tr.parent_id) AS parent_object, sm.definition
FROM sys.triggers tr
LEFT JOIN sys.sql_modules sm ON tr.object_id = sm.object_id
ORDER BY tr.parent_class_desc, tr.name;

Use OBJECT_DEFINITION(OBJECT_ID('schema.TriggerName')) or sp_helptext to view a trigger; disable a trigger for testing only with DISABLE TRIGGER name ON <table|DATABASE|ALL SERVER>. (dba.stackexchange.com)

Fix patterns that avoid aborting the caller’s transaction: do not use BEGIN TRAN/COMMIT inside triggers; instead use SAVE TRAN and ROLLBACK TRAN <savepoint> to undo only trigger work, or RAISERROR (SQL 2008 R2) to signal validation failures and RETURN. Wrap complex trigger logic in TRY/CATCH, inspect XACT_STATE() before attempting commits, and re-raise errors with RAISERROR inside the CATCH (THROW is introduced later). These techniques prevent leaving the outer transaction in a doomed state and make the failure mode explicit. Example savepoint pattern shown in many trigger best-practice guides. (flylib.com)

The follow-up by asking for the exact error text is the right next diagnostic move: the full Msg number and the trigger DDL are the smallest, most useful bits of data to pinpoint whether the trigger is intentionally rolling back, hitting an unhandled exception, or invoking server/database-level rules (DDL triggers, policies). Also verify compatibility level and run the SQL Server Upgrade Advisor during a migration. (learn.microsoft.com)

Member Avatar for Member #949455

When I try to migrate from SQL Server 2000 to SQL Server 2008 R2 Express, when I run the application program there is an error message transaction aborted by the trigger, whereas before when I was using SQL Server 2000 does not exist error.

Can you post the error code message?

I don't quite understand your view of the error.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.