?
Solved

Message 3609: The transaction ended in the trigger

Posted on 2009-04-20
9
Medium Priority
?
1,744 Views
Last Modified: 2012-05-06
have a SQL 2000 trigger but when I run it SQL 2005 I get the following error

The transaction ended in a trigger

How do I get the trigger to run properly?

Thanks in advance...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trigPrevQuarters]
on [dbo].[table3]
after update
as
 
declare @int int
 
declare @year as varchar(4)
declare @UpdDatequarter varchar(12)
declare @monthVar varchar (2)
declare @month datetime
 
declare @qurter as varchar(12)
declare @combinedYm as varchar(6)
 
set @year = (select year(getdate()))
set @monthVar = (select month(getdate()))
 
if @monthVar <=3
set @year = @year-1 
 
set @month = (select case @monthVar%3
   	when 0 then  (dateadd( m, -3, getdate()))
   	when 1 then  (dateadd( m, -1, getdate()))
   	when 2 then  (dateadd( m, -2, getdate()))
 end )
 
if len(cast(month(@month)as varchar (2)))< 2 
set @monthVar = '0'+ cast(month(@month)as varchar (2))
 
set @combinedYm =  cast(@year as varchar(4)) + '' + @monthVar
 
set @int = (select distinct field1 from inserted)
set @qurter = (select field2 from inserted)
 
set @UpdDatequarter = (select field3 from table3 where field1 = @int) 
 
if (update (Value1) and @UpdDatequarter < @combinedYM)
begin
rollback transaction
 
end

Open in new window

0
Comment
Question by:HeitmanProgrammers
  • 5
  • 4
9 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24187623
>rollback transaction
that has indeed changed between sql 2000 and sql 2005

if you just want to just silently ignore that transaction, you need to run the trigger as "instead of", and run the transaction you want to run when you do NOT want to rollback.
0
 
LVL 1

Author Comment

by:HeitmanProgrammers
ID: 24187856
Since this is "AFTER UPDATE' trigger, I want to rollback the transaction when the "if" statement fails. In another words, when the conditions in the "if" statement is met, I would like to continue the update statement.

Would the code below be the right script fot it?
**************************************************************************************************
CREATETRIGGER [dbo].[trigPrevQuarters]
on [dbo].[table3]
INSTEAD OF UPDATE

as
 
BEGIN
declare @int int
 
declare @year as varchar(4)
declare @UpdDatequarter varchar(12)
declare @monthVar varchar (2)
declare @month datetime
 
declare @qurter as varchar(12)
declare @combinedYm as varchar(6)
 
set @year = (select year(getdate()))
set @monthVar = (select month(getdate()))
 
if @monthVar <=3
set @year = @year-1
 
set @month = (select case @monthVar%3
         when 0 then  (dateadd( m, -3, getdate()))
         when 1 then  (dateadd( m, -1, getdate()))
         when 2 then  (dateadd( m, -2, getdate()))
 end )
 
if len(cast(month(@month)as varchar (2)))< 2
set @monthVar = '0'+ cast(month(@month)as varchar (2))
 
set @combinedYm =  cast(@year as varchar(4)) + '' + @monthVar
 
set @int = (select distinct field1 from inserted)
set @qurter = (select field2 from inserted)
 
set @UpdDatequarter = (select field3 from table3 where field1 = @int)
 
if (update (Value1) and @UpdDatequarter < @combinedYM)
  'include the update statement to update the table
END
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24187876
actually, it would be:


if NOT( (update (Value1) and @UpdDatequarter < @combinedYM))
  'include the update statement to update the table
END

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:HeitmanProgrammers
ID: 24187952
Why do I need to use NOT for the statement... I actually want to update the table when the condition in the if statement = TRUE.

Thanks.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24188011
in your original code, you had:

if (update (Value1) and @UpdDatequarter < @combinedYM)
begin
  rollback transaction
end

so, you want the opposite condition to actually execute the UPDATE, don't you?
0
 
LVL 1

Author Comment

by:HeitmanProgrammers
ID: 24188022
Understood... I had not copied my code correctly. Let me try this and I will get back to you.

Thanks.
0
 
LVL 1

Author Comment

by:HeitmanProgrammers
ID: 24188131
My questions is:

How do I reference the INSERTED table and include an update statement which is updating about 20 fields in a table?

Thanks
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24188411
it would looks like this:
if NOT ( (update (Value1) and @UpdDatequarter < @combinedYM))
begin
  UPDATE t 
    SET col1 = i.col1
      , col2 = i.col2
      , col3 = i.col3
      , col4 = i.col4
       ... etc ...
    FROM [dbo].[table3] t
    JOIN INSERTED i
      ON i.primary_key = t.primary_key
end

Open in new window

0
 
LVL 1

Author Comment

by:HeitmanProgrammers
ID: 24188446
That's what I tried and it worked for me.

Thank you very much for your assistance.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question